Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cross referenceing data in excel
I have two data sets in excel, each with the same unique id. I am trying to
enrich the data from one data set with the other. Therefore if for example I have name and address details & the unique ID on one sheet AND I have say annual spend and unique ID on another sheet. I want to combine the data sets using the unique ID as the identifier and create one whole data set. ie. have one data set with unique ID, name & Address and spend on one sheet Can this be done? |
#2
|
|||
|
|||
VLOOKUP can give you just what you wantSuppose we have a table of pet IDs,
pet names and pet types starting in A1: 100 oscar dog 500 daisey cat 55 rover dog 34 mini goldfish and another table of pet IDs and pet ages starting in A7: 500 6 100 4 34 1 55 12 To combine the tables in D1 put =VLOOKUP(A1,$A$7:$B$10,2,0) and copy down. -- Gary's Student "Dave O'Connor" wrote: I have two data sets in excel, each with the same unique id. I am trying to enrich the data from one data set with the other. Therefore if for example I have name and address details & the unique ID on one sheet AND I have say annual spend and unique ID on another sheet. I want to combine the data sets using the unique ID as the identifier and create one whole data set. ie. have one data set with unique ID, name & Address and spend on one sheet Can this be done? |
#3
|
|||
|
|||
One way using INDEX and MATCH..
Assume in Sheet1, data is in cols A to C, from row2 down with unique IDs in col C Name Add ID Nam1 Ad1 ID1 Nam2 Ad2 ID2 Nam3 Ad3 ID3 Nam4 Ad4 ID4 In Sheet2, data is in cols A and B, from row2 down with unique IDs in col B Spend ID 100 ID1 200 ID2 300 ID3 400 ID4 Let's bring over Spend from Sheet2 into col D in Sheet1 In Sheet1 --------- Put in D2: =IF(ISNA(MATCH(Sheet1!C2,Sheet2!B:B,0)),"", INDEX(Sheet2!A:A,MATCH(Sheet1!C2,Sheet2!B:B,0))) Copy D2 down as far as required, and you'd get: Name Add ID Spend Nam1 Ad1 ID1 100 Nam2 Ad2 ID2 200 Nam3 Ad3 ID3 300 Nam4 Ad4 ID4 400 etc Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Dave O'Connor" <Dave wrote in message ... I have two data sets in excel, each with the same unique id. I am trying to enrich the data from one data set with the other. Therefore if for example I have name and address details & the unique ID on one sheet AND I have say annual spend and unique ID on another sheet. I want to combine the data sets using the unique ID as the identifier and create one whole data set. ie. have one data set with unique ID, name & Address and spend on one sheet Can this be done? |
#4
|
|||
|
|||
Hi, If your ID column is the first column in your data sets then you can use a VLOOKUP, if not then use a combination of INDEX and MATCH. Say your ID #'s are in column A on the main sheet and your ID #'s in column A and annual spend data in column B of sheet 2 then: =VLOOKUP(A1,Sheet2!A1:B10,2,0) If your annual spend is in say column A and your ID #'s in column B then: =INDEX(Sheet2!A1:A10,MATCH(A1,Sheet2!B1:B10,0)) Hope this helps! JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=474578 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel Data from One Sheet to Another.... | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
pasting excel data in a powerpoint slide | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |