Merging Excel sheet merge by matching cells?

Question

Here's what I have:

Sheet A has multiple rows, each row has an ID# that may or may not be in Sheet B, which has an ID# that may or may not be in Sheet C

I want to merge all three sheets so that matching ID#s and anything in that row are merged into a single row.

Example:

Sheet A

1 ID2476 Jill Thompson

2 ID2477 Ed Williams

3 ID2466 Tom Smith

Sheet B

1 ID2467 11572

2 ID2477 11566

Sheet C

1 ID2467 212-555-1212

2 ID2477 212-555-0012

Result merge

1 ID2476 Jill Thompson, 11572, 212-555-1212

2 ID2477 Ed Williams, 11566, 212-555-0012

3 ID2466 Tom Smith

I will consider solutions that I can do on my own or with the help of a add-on tool that I can buy, or even an Excel expert who is open to doing this for me.

Answer 1

you could do this with some vlookup formulas. A lot will depend on how your data is actually laid out.  Also, is there one sheet that has all ID's on it (ie: a master list in which every id is listed even though there may not be match in the other sheets)?

Assuming that the ID and name are in separate columns (and same of each of the other sheets), and assuming that sheet A has a list of all possible ID's that may or may not be on the other sheets.  And for this example, I'll assume that each sheet's data starts in row 1, column A

so in sheet A you have ID and name. In column A and B.

in column C enter

=VLOOKUP(A1, SHEETB!$A$1:$B$1000, 2, FALSE)

this will look up the ID from A1 and try to find a match in sheetB, range of A1 thru B1000 (or set it whatever your actual range is). If it finds a match it will return the data from the 2nd column.  The false at the end just tells excel to only fnd exact matches).  If it doesn't find a match it'll return a #NA.

now in column D enter the same formula, but look for the id in sheetC

=VLOOKUP(A1, SHEETC!$A$1:$B$1000, 2, FALSE)

copy/paste these formulas down as far you need to in sheetA

so now you should have

ID in A1

Name in B1

zipcode(?) in C1

phone# in D1

now in E1 just "merge" (concatinate everything together)

=A1&" "&B1&" "&C1&" "&D1

this "adds" them togehter, with spaces in between each entry.

now you could copy all these formulas and do a "paste special" and choose values and paste all this data in some new sheet.  The paste special/values will paste the results of the formula rather than the actual formula.

Tip: if you have a lot of #NA results, you could test for NA first, and then either return the value, or leave the cell blank.  example:

=IF(ISNA(VLOOKUP(A1, SHEETC!$A$1:$B$1000, 2, FALSE)),"", VLOOKUP(A1, SHEETC!$A$1:$B$1000, 2, FALSE))

this if formula basically says IF the vlookup is NA (ie: no match), then leave the cell blank, otherwise do the vlookup

Assumption #2.  If the data in each of these cells are not in seperate columns (ie: ID and Tom Smith are all in one cell), you'll need to separate them out first.  You can do this with a formula.  So if A1 has ID2476 Tom Smith

in B1 enter a formula of

=LEFT(A1, 6)  (takes the 1st 6 characters, starting from the left)

in C1 enter

=MID(A1, 8, LEN(A1))

this takes the remaining characters, starting from the 8th position,

now each item is in separate cells.  Do this for all the other sheets. Then adjust the vlookup examples above accordingly.

if you still need some help, you can email a copy of file to me at [e-mail] and I'll take a look and see that I can do.