Help! How do you merge two Excel spreadsheets?

[Ray P. Light]

I have a series of separate databases of client information, where
each database shares a common client ID number. I need to find a way
to connect these separate spreadsheets by linking them by the common
client ID number.

For example, I have one spreadsheet that has hundreds of cases with
data arrayed as such (note: there may be missing data):

Client ID Var1 Var2 Var3

ABC123 5 4 6
KLM324 3 4
NOP890 2 1 5

And then another spreadsheet that has hundreds of cases with data
arrayed as such (note: may have extra case, and they are in a
different order):

Client ID Var4 Var5 Var6

KLM324 2 2 7
NOP890 4 1 1
ABC123 6 8 3
KRE956 5 3 3

I want to have a way to have Excel merge them so I end up with:

Client ID Var1 Var2 Var3 Var4 Var5 Var6

ABC123 5 4 6 6 8 3
KLM324 3 4 2 2 7
NOP890 2 1 5 4 1 1
KRE956 5 3 3

Help!!

 

[Guest]

say you add columns var4, 5 , 6 in book1. then place this
in 1st cell in column with var4 (which in your example is
2nd column in the selected range A1100) so for var5
change that to 3. n so on.

=VLOOKUP(A2,'C:\...\[Book1.xls]'!$A$1:$D$100,2,0)

i assume clients id's are in coulmn A in each workbook (it
has to be the first column in the range) and the var.
columns are on the right side of it.
for more info go to
http://www.cpearson.com/excel/lookups.htm

 

[MPBroida]

I have a series of separate databases of client information, where
each database shares a common client ID number. I need to find a way
to connect these separate spreadsheets by linking them by the common
client ID number.

For example, I have one spreadsheet that has hundreds of cases with
data arrayed as such (note: there may be missing data):

Client ID Var1 Var2 Var3

ABC123 5 4 6
KLM324 3 4
NOP890 2 1 5

And then another spreadsheet that has hundreds of cases with data
arrayed as such (note: may have extra case, and they are in a
different order):

Client ID Var4 Var5 Var6

KLM324 2 2 7
NOP890 4 1 1
ABC123 6 8 3
KRE956 5 3 3

I want to have a way to have Excel merge them so I end up with:

Client ID Var1 Var2 Var3 Var4 Var5 Var6

ABC123 5 4 6 6 8 3
KLM324 3 4 2 2 7
NOP890 2 1 5 4 1 1
KRE956 5 3 3

Help!!

Assuming that you are using "spreadsheet" to mean an
entire .xls file, then you should be able to COPY the
WORKSHEET from one .xls file into the other .xls file,
resulting in multiple worksheets in one file. Each
WORKSHEET will be a separate tab near the bottom of the
Excel window.

AFTER both worksheets are in one file, I'm sure someone
here can suggest a way to get data from one worksheet
onto the other. It might require some coding, though.

Mike

 

[Dylan Hunter]

Hey Ray,

Luckily, your answer's fairly simple - just take a look at the VLOOKUP
function, (presuming Excel). Key it on Client ID, and it'll bring back
just the data you want from whatever files you want, attributed to the
correct customer.

Good luck,

Dylan