Results 1 to 8 of 8

Thread: Excel Formula Help

  1. 29-09-2006, 11:51 AM #1
    Lead_Head
    Lead_Head is offline
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    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

    Wanted! Car Registration Plate ' LYL 4K ' Can you help?
    Reply With Quote Reply With Quote
  2. 29-09-2006, 01:08 PM #2
    cotswoldcs
    cotswoldcs is offline
    Nothing runs like a Deere cotswoldcs's Avatar
    Join Date
    Mar 2004
    Location
    Bang in the heart of the cotswolds
    Posts
    793
    Thanks
    40
    Thanked
    18 times in 18 posts
    • cotswoldcs's system
      • Motherboard:
      • Asus P4C800-E Deluxe
      • CPU:
      • Pentium 4 3.0Ghz Northwood (@3.4Ghz on water)
      • Memory:
      • 1.5Gb Corsair TwinX PC3200/PC3700
      • Storage:
      • 150Gb Raptor
      • Graphics card(s):
      • XFX 6600GT 128Mb (w waterblock)
      • PSU:
      • Seasonic S12-380
      • Case:
      • Antec Sonata I
      • Monitor(s):
      • 3 x Iiyama 19" LCD (5:4) on Comrac Tripple Mount
      • Internet:
      • PlusNet 1.6Mb ADSL
    I think a PivotTable would solve your problem. Have you used the technique before?
    Reply With Quote Reply With Quote
  3. 29-09-2006, 01:12 PM #3
    Allen
    Allen is offline
    HEXUS.social member Allen's Avatar
    Join Date
    Nov 2003
    Location
    Brighton
    Posts
    8,536
    Thanks
    363
    Thanked
    262 times in 168 posts
    • Allen's system
      • Motherboard:
      • ASUS Maximus VIII Gene
      • CPU:
      • Intel Core i5 6600K
      • Memory:
      • 2 x 8GB Kingston HyperX Predator DDR4-3000
      • Storage:
      • 256GB Samsung 950 PRO NVMe M.2 (OS) + 2 x 512GB Samsung 960 EVO in RAID 0 (Games)
      • Graphics card(s):
      • ASUS ROG Strix GeForce GTX 1080 Ti OC
      • PSU:
      • XFX P1-650X-NLG9 XXX 650W Modular
      • Case:
      • Fractal Design Node 804
      • Operating System:
      • Windows 10 Home 64-bit
      • Monitor(s):
      • 27" BenQ XL2730Z + 23" Dell U2311H
      • Internet:
      • Virgin Media 200Mbps
    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?
    Reply With Quote Reply With Quote
  4. 29-09-2006, 01:30 PM #4
    Pealy
    Pealy is offline
    Member
    Join Date
    Aug 2005
    Location
    Lancashire, UK
    Posts
    145
    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.
    Reply With Quote Reply With Quote
  5. 29-09-2006, 01:30 PM #5
    Lead_Head
    Lead_Head is offline
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    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.

    Wanted! Car Registration Plate ' LYL 4K ' Can you help?
    Reply With Quote Reply With Quote
  6. 29-09-2006, 01:32 PM #6
    Lead_Head
    Lead_Head is offline
    Senior Member
    Join Date
    Jul 2003
    Location
    Nott'm, East Midlands
    Posts
    1,954
    Thanks
    0
    Thanked
    0 times in 0 posts
    Quote 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?

    Wanted! Car Registration Plate ' LYL 4K ' Can you help?
    Reply With Quote Reply With Quote
  7. 29-09-2006, 01:34 PM #7
    Lakeuk
    Lakeuk is offline
    Member
    Join Date
    Feb 2006
    Location
    Lake District
    Posts
    69
    Thanks
    0
    Thanked
    0 times in 0 posts
    • Lakeuk's system
      • Motherboard:
      • Gigabyte P965-DS4
      • CPU:
      • Core 2 Duo E6600
      • Memory:
      • 2Gb standard spec
      • Storage:
      • 2 x 250Gb, 1 x 160Gb plus external 320Gb for backups
      • Graphics card(s):
      • fanless 7600GS
      • PSU:
      • 450watt standard came with case
      • Case:
      • Antec Sonata II
      • Monitor(s):
      • Samsung 940BF - 19" 2ms
      • Internet:
      • Demon 8Mb
    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
    Reply With Quote Reply With Quote
  8. 02-10-2006, 10:09 AM #8
    Pealy
    Pealy is offline
    Member
    Join Date
    Aug 2005
    Location
    Lancashire, UK
    Posts
    145
    Thanks
    0
    Thanked
    0 times in 0 posts
    Quote 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!
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Showing formula in Calc (Open Office)
    By XA04 in forum Help! Quick Relief From Tech Headaches
    Replies: 3
    Last Post: 22-09-2006, 09:32 PM
  2. Excel windows
    By Steve in forum Software
    Replies: 4
    Last Post: 25-07-2006, 12:44 PM
  3. Excel help - removing '0' results from formula
    By tim_n in forum Software
    Replies: 5
    Last Post: 20-07-2006, 09:05 AM
  4. IE Opens Excel Spreadsheet In Web, Not Excel
    By Vini in forum Software
    Replies: 6
    Last Post: 12-08-2005, 08:50 AM
  5. Racing Games - local circuits, Formula Cars (pref)
    By ikonia in forum Gaming
    Replies: 1
    Last Post: 16-06-2005, 07:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules