• HEXUS
  • HEXUS.tv
  • channel
  • gaming
  • lifestyle
  • trust
  • community
  • ESReality
    • HEXUS.community discussion forumsVisit Corsair.com

    Welcome to the HEXUS.community discussion forums forums.

    You are currently viewing our boards as a guest which gives you limited access to view most discussions and other features. By joining our free community you will have access to post topics, respond to polls and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!

    Go Back   HEXUS.community discussion forums > HEXUS.help - buying advice & technical queries > Operating systems & applications
    Excel Formula Help Excel Formula Help
    Register FAQ Calendar Search Today's Posts Mark Forums Read

    Operating systems & applications Looking for that application for Windows? needing advice with your Linux setup? - ask here! Add RSS Feed

    Reply
     
    		LinkBack Thread Tools
    Old 29-09-2006, 10:51 AM   #1 (permalink)
    Senior Member
     
    Join Date: Jul 2003
    Location: Nott'm, East Midlands
    Posts: 1,955
    Thanks: 0
    Thanked 0 Times in 0 Posts
    Excel Formula Help
    I've got a set of data in Excel that I want to fill in with data from another tab via VLOOKUP. Easy I hear you say, but I want to use two different values to VLOOKUP from.

    For example,

    I want to filter on a product, for arguments sake let's call it 'MOBO' and how many we sold on a particular date.

    So my raw data will look something like the below

    Code:
    	             COLUMN1 COLUMN2 COLUMN3   COLUMN4	Total

MOBO	01-Aug-06	430	466	40	1	937
MOBO	02-Aug-06	430	466	40	1	937
MOBO	03-Aug-06	430	466	40	1	937
MOBO	04-Aug-06	430	466	40	1	937
MOBO	05-Aug-06	430	466	40	1	937
MOBO	06-Aug-06	430	466	40	1	937
MOBO	07-Aug-06	428	455	40	1	924
MOBO	08-Aug-06	427	454	40	1	922
MOBO	09-Aug-06	425	453	40	1	919
MOBO	10-Aug-06	425	453	40	1	919
MOBO	11-Aug-06	425	452	40	1	918
MOBO	12-Aug-06	425	452	40	1	918
MOBO	13-Aug-06	425	452	40	1	918
MOBO	14-Aug-06	420	402	40	1	863
MOBO	15-Aug-06	420	402	40	1	863
MOBO	16-Aug-06	419	399	40	1	859
MOBO	17-Aug-06	419	399	40	1	859
MOBO	18-Aug-06	419	398	40	1	858
MOBO	19-Aug-06	418	398	40	1	857
MOBO	20-Aug-06	418	398	40	1	857
MOBO	21-Aug-06	414	364	40	1	819
MOBO	22-Aug-06	413	364	40	1	818
MOBO	23-Aug-06	413	364	40	1	818
MOBO	24-Aug-06	413	364	40	1	818
MOBO	25-Aug-06	412	364	40	1	817
MOBO	26-Aug-06	411	364	40	1	816
MOBO	27-Aug-06	411	364	40	1	816
MOBO	28-Aug-06	407	341	40	1	789
MOBO	29-Aug-06	407	341	40	1	789
MOBO	30-Aug-06	407	341	40	1	789
MOBO	31-Aug-06	406	341	40	1	788

    As you can see the data fluctuates on a day to day basis. Now, I want to reflect this data on another tab via a LOOKUP query.

    How do I do this? The data on the other tab is also split by dates. So therefore I need to use a VLOOKUP query using the date and 'MOBO'?

    Thanks for any help fellas
    Lead_Head is offline   Reply With Quote
    Old 29-09-2006, 12:08 PM   #2 (permalink)
    Nothing runs like a Deere
     
    cotswoldcs's Avatar
     
    Join Date: Mar 2004
    Location: Bang in the heart of the cotswolds
    Posts: 723
    Thanks: 38
    Thanked 17 Times in 17 Posts
    cotswoldcs's system
    I think a PivotTable would solve your problem. Have you used the technique before?
    cotswoldcs is offline   Reply With Quote
    Old 29-09-2006, 12:12 PM   #3 (permalink)
    Big Member
     
    Join Date: Nov 2003
    Location: London
    Posts: 6,846
    Thanks: 84
    Thanked 60 Times in 33 Posts
    Allen's system
    I'm a bit confused with why you are wanting to do this. A simple Autofilter will help, surely?

    Or am I not reading your question correctly? Could you maybe give a bit more info exactly what you have and what you want?
    If cars had followed the same developmental path as computers, a Rolls Royce would cost £60, get a million miles per gallon and explode once a year, killing everyone inside.
    Allen is offline   Reply With Quote
    Old 29-09-2006, 12:30 PM   #4 (permalink)
    Member
     
    Join Date: Aug 2005
    Location: Lancashire, UK
    Posts: 137
    Thanks: 0
    Thanked 0 Times in 0 Posts
    Whenever I've needed to do this I've had to add a column to each table which is a concatonation of the items I need to look-up. That's the limit of Excel's lookups.
    Pealy is offline   Reply With Quote
    Old 29-09-2006, 12:30 PM   #5 (permalink)
    Senior Member
     
    Join Date: Jul 2003
    Location: Nott'm, East Midlands
    Posts: 1,955
    Thanks: 0
    Thanked 0 Times in 0 Posts
    I have 2 tabs in an excel spreadsheet, one is the raw data (below):

    Code:
    TYPE        DATE             COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
MOBO	01-Aug-06	430	466	40	1	937
MOBO	02-Aug-06	430	466	40	1	937
MOBO	03-Aug-06	430	466	40	1	937
MOBO	04-Aug-06	430	466	40	1	937
MOBO	05-Aug-06	430	466	40	1	937
MOBO	06-Aug-06	430	466	40	1	937
MOBO	07-Aug-06	428	455	40	1	924
MOBO	08-Aug-06	427	454	40	1	922
MOBO	09-Aug-06	425	453	40	1	919
MOBO	10-Aug-06	425	453	40	1	919
MOBO	11-Aug-06	425	452	40	1	918
MOBO	12-Aug-06	425	452	40	1	918
MOBO	13-Aug-06	425	452	40	1	918
MOBO	14-Aug-06	420	402	40	1	863
MOBO	15-Aug-06	420	402	40	1	863
MOBO	16-Aug-06	419	399	40	1	859
MOBO	17-Aug-06	419	399	40	1	859
MOBO	18-Aug-06	419	398	40	1	858
MOBO	19-Aug-06	418	398	40	1	857
MOBO	20-Aug-06	418	398	40	1	857
MOBO	21-Aug-06	414	364	40	1	819
MOBO	22-Aug-06	413	364	40	1	818
MOBO	23-Aug-06	413	364	40	1	818
MOBO	24-Aug-06	413	364	40	1	818
MOBO	25-Aug-06	412	364	40	1	817
MOBO	26-Aug-06	411	364	40	1	816
MOBO	27-Aug-06	411	364	40	1	816
MOBO	28-Aug-06	407	341	40	1	789
MOBO	29-Aug-06	407	341	40	1	789
MOBO	30-Aug-06	407	341	40	1	789
MOBO	31-Aug-06	406	341	40	1	788
    Now, I have many sets of the raw data on a raw data tab above (e.g. CPU, PSU where it says MOBO above) and you are not guaranteed to get MOBO in the raw data, so the data is not static in this tab. I.e. I can't just put a formula like =A142, as cell A142 next time will be a different date (30 and 31 day months for example).

    The tab I want it showing on in Excel needs to look like this:
    Code:
    DATE             COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
01-Aug-06	430	466	40	1	937
02-Aug-06	430	466	40	1	937
03-Aug-06	430	466	40	1	937
04-Aug-06	430	466	40	1	937
05-Aug-06	430	466	40	1	937
06-Aug-06	430	466	40	1	937
07-Aug-06	428	455	40	1	924
08-Aug-06	427	454	40	1	922
09-Aug-06	425	453	40	1	919
10-Aug-06	425	453	40	1	919
11-Aug-06	425	452	40	1	918
12-Aug-06	425	452	40	1	918
13-Aug-06	425	452	40	1	918
14-Aug-06	420	402	40	1	863
15-Aug-06	420	402	40	1	863
16-Aug-06	419	399	40	1	859
17-Aug-06	419	399	40	1	859
18-Aug-06	419	398	40	1	858
19-Aug-06	418	398	40	1	857
20-Aug-06	418	398	40	1	857
21-Aug-06	414	364	40	1	819
22-Aug-06	413	364	40	1	818
23-Aug-06	413	364	40	1	818
24-Aug-06	413	364	40	1	818
25-Aug-06	412	364	40	1	817
26-Aug-06	411	364	40	1	816
27-Aug-06	411	364	40	1	816
28-Aug-06	407	341	40	1	789
29-Aug-06	407	341	40	1	789
30-Aug-06	407	341	40	1	789
31-Aug-06	406	341	40	1	788
    But it looks like this using a VLOOKUP formula I'm using at the moment.

    Code:
    DATE   COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
01/08/06	430	466	40	1	937
02/08/06	430	466	40	1	937
03/08/06	430	466	40	1	937
04/08/06	430	466	40	1	937
05/08/06	430	466	40	1	937
06/08/06	430	466	40	1	937
07/08/06	430	466	40	1	937
08/08/06	430	466	40	1	937
09/08/06	430	466	40	1	937
10/08/06	430	466	40	1	937
11/08/06	430	466	40	1	937
12/08/06	430	466	40	1	937
13/08/06	430	466	40	1	937
14/08/06	430	466	40	1	937
15/08/06	430	466	40	1	937
16/08/06	430	466	40	1	937
17/08/06	430	466	40	1	937
18/08/06	430	466	40	1	937
19/08/06	430	466	40	1	937
20/08/06	430	466	40	1	937
21/08/06	430	466	40	1	937
22/08/06	430	466	40	1	937
23/08/06	430	466	40	1	937
24/08/06	430	466	40	1	937
25/08/06	430	466	40	1	937
26/08/06	430	466	40	1	937
27/08/06	430	466	40	1	937
28/08/06	430	466	40	1	937
29/08/06	430	466	40	1	937
30/08/06	430	466	40	1	937
31/08/06	430	466	40	1	937
	13330	14446	1240	31
    As you can see it is picking up the first value for each date, which is incorrect. The idea is to create an automated solution to just paste in data to the Raw Data tab and the other tabs automatically fill.

    I use pivot tables quite a lot and sadly I cannot use a pivot table as part of this solution.
    Lead_Head is offline   Reply With Quote
    Old 29-09-2006, 12:32 PM   #6 (permalink)
    Senior Member
     
    Join Date: Jul 2003
    Location: Nott'm, East Midlands
    Posts: 1,955
    Thanks: 0
    Thanked 0 Times in 0 Posts
    Originally Posted by Pealy View Post
    Whenever I've needed to do this I've had to add a column to each table which is a concatonation of the items I need to look-up. That's the limit of Excel's lookups.
    So you would concatinate the date and product in this instance?
    Lead_Head is offline   Reply With Quote
    Old 29-09-2006, 12:34 PM   #7 (permalink)
    Member
     
    Join Date: Feb 2006
    Location: Lake District
    Posts: 69
    Thanks: 0
    Thanked 0 Times in 0 Posts
    Lakeuk's system
    Can't be done with vlookup, found a link that indicates that INDEX/MATCH should be used but not tried it

    http://www.mvps.org/dmcritchie/excel/vlookup.htm
    Lakeuk is offline   Reply With Quote
    Old 02-10-2006, 09:09 AM   #8 (permalink)
    Member
     
    Join Date: Aug 2005
    Location: Lancashire, UK
    Posts: 137
    Thanks: 0
    Thanked 0 Times in 0 Posts
    Originally Posted by Lead_Head View Post
    So you would concatinate the date and product in this instance?
    I'm still struggling to understand exactly what you're trying to do so I'll take a wild guess. Are you saying that you will specify a 'Type' (maybe have one results tab for each type?) and you want to see the entry for that type for each day?

    Eg. if your data is:

    TYPE DATE COLUMN1 COLUMN2 COLUMN3 COLUMN4 TOTAL
    MOBO 01-Aug-06 430 466 50 9 955
    MOBO 02-Aug-06 431 467 60 8 966
    CPU 01-Aug-06 432 468 70 7 977
    CPU 02-Aug-06 433 469 80 6 988
    PSU 02-Aug-06 434 470 90 5 999
    MOBO 03-Aug-06 435 471 10 4 920

    They you want to see different sets of data. Eg on Tab 1 (for MOBO) you just see:

    MOBO 01-Aug-06 430 466 50 9 955
    MOBO 02-Aug-06 431 467 60 8 966
    MOBO 03-Aug-06 435 471 10 4 920

    But on Tab2 (for CPU) you want to see

    CPU 01-Aug-06 432 468 70 7 977
    CPU 02-Aug-06 433 469 80 6 988
    CPU 03-Aug-06 0 0 0 0 0

    And on Tab3 (PSU)

    PSU 01-Aug-06 0 0 0 0 0
    PSU 02-Aug-06 434 470 90 5 999
    PSU 03-Aug-06 0 0 0 0 0

    I've knocked up a simple spreadsheet which does the abobve but I may have the wrong end of the stick and LakeUK could be right!
    Pealy is offline   Reply With Quote
    Reply

    Breadcrumb
    Go Back   HEXUS.community discussion forums > HEXUS.help - buying advice & technical queries > Operating systems & applications
    Excel Formula Help Excel Formula Help

    « Previous Thread | Next Thread »

    Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
     
    Thread Tools

    Posting Rules
    You may not post new threads
    You may not post replies
    You may not post attachments
    You may not edit your posts
    BB code is On
    Smilies are On
    [IMG] code is On
    HTML code is Off
    Trackbacks are On
    Pingbacks are On
    Refbacks are On

    Similar Threads
    Thread Thread Starter Forum Replies Last Post
    Showing formula in Calc (Open Office) XA04 Help - technical & advisory 3 22-09-2006 08:32 PM
    Excel windows Steve Operating systems & applications 4 25-07-2006 11:44 AM
    Excel help - removing '0' results from formula tim_n Software and web development 5 20-07-2006 08:05 AM
    IE Opens Excel Spreadsheet In Web, Not Excel Vini Software and web development 6 12-08-2005 07:50 AM
    Racing Games - local circuits, Formula Cars (pref) ikonia HEXUS.gaming 1 16-06-2005 06:35 PM



    All times are GMT. The time now is 10:32 AM.

    Contact Us - HEXUS - UK Technology News and Reviews - Archive - Top
    Any representations/statements made on the HEXUS.community discussion forums are the representations/statements of the author i.e. the person/organisation making them. If any such representations/statements are disputed they are a matter between the parties concerned.
    HEXUS Limited accepts no responsibility for any misrepresentations, inaccurate or false statements made by any person/organisation other than HEXUS Limited employees.
    For more information please read HEXUS Limited's terms, conditions and privacy policy.

    Hosted Exchange

    Powered by vBulletin® Version 3.8.4
    Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
    Content Relevant URLs by vBSEO 3.3.2
    © Copyright 2009 HEXUS® Limited. All rights reserved. Unauthorised reproduction strictly prohibited.