Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dave O'Connor
 
Posts: n/a
Default 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   Report Post  
Gary''s Student
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
pinmaster
 
Posts: n/a
Default


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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
Getting Excel Data from One Sheet to Another.... Robin Excel Discussion (Misc queries) 2 April 21st 05 01:15 PM
How can deleted data reappear in a refreshed pivot table in Excel excel_user123456 Excel Discussion (Misc queries) 3 February 23rd 05 09:34 PM
pasting excel data in a powerpoint slide James Excel Discussion (Misc queries) 4 January 28th 05 03:23 PM
Help with data not getting plotted Scott Ehrlich Excel Discussion (Misc queries) 2 January 25th 05 06:17 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 06:55 PM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"