How do you merge worksheets
 

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

How do you merge worksheets
 

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:FPaste SpecialValues to convert the formulae to data
If the name needs to be next to the existing name, then Copy column Fmove
cursor to select column CInsert 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
--

Regards
Roger Govier

"Kate" wrote in message
...
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