How do you merge worksheets

[Kate]

would like to merge data from one workwheet into another and combine the
data. Both WS have a account # as one the column. Would like all the data
associated with Acc# 123 in WS 1 to combine with all the data in WS 2 for
Acc# 123 to merge.

WS 1 WS 2
Acc# Name Amount Acc# City
QTY Name
123 Jones 39.95 123
Anytown 4 Jones

This is what I would like to happen....can this be done? Kate

Merged WS1 and WS 2

Acc# Name Name Amount City QTY
123 Jones Jones 39.95 Anytown 4

 

[Roger Govier]

Hi Kate

On sheet1 in cell D2 enter
=VLOOKUP($A2,Sheet2!$A:$C,COLUMN(B1),0)
Copy across to E2
Copy D2:E2 down sheet1 for the full extent of your data

I am assuming you don't really mean you want the name twice.
If you do, extend the formulae to column F as well.

Copy columns D:F>Paste Special>Values to convert the formulae to data
If the name needs to be next to the existing name, then Copy column F>move
cursor to select column C>Insert Cut cells.

If there are going to be values missing from Sheet2 compared with Sheet1,
then wrap the formula in a conditional test
=IF(ISNA(VLOOKUP($A2,Sheet2!$A:$C,COLUMN(B1),0)),"",
VLOOKUP($A2,Sheet2!$A:$C,COLUMN(B1),0))

Change Sheet1 and Sheet2 to the names of your sheets, remembering to enclose
the names in single quotes if there are spaces in the sheet name
e.g.
VLOOKUP(A2,'My Sheet 1'!$A:$C