Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge worksheets | Excel Worksheet Functions | |||
Merge Worksheets | Excel Discussion (Misc queries) | |||
Merge Worksheets | Excel Worksheet Functions | |||
Merge Worksheets | Excel Worksheet Functions | |||
how to merge all worksheets into one | Excel Worksheet Functions |