Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
merging data from two different sheets
As Cantor of a large synagogue, I'd like to have membership data on my palm
pilot. My office provided me with two files: 1. Includes ID, names, addresses, birthdates, phones...etc. 2. Includes ID, children's names and birthdates. Using the ID as the common link, how can I easily merge the correct children's names with parental records, creating one file which I can import to Outlook and thus sync with my palm. Appreciate your help! Robert |
#2
|
|||
|
|||
Assume that you will use the table with most info (number 1 in your
description) Assume also for simplicity that the first ID starts in A2 with a ID header in A1 (still in the 1st file) Now in the first empty adjacent column to the right in row 2 put =VLOOKUP($A2,[children.xls]Sheet1!$A$2:$E$200,COLUMN(B:B),0) where A2 holds the ID in the main table that will be looked up in the children table now copy the formula across as long as needed, if the children table is 4 columns across then copy 3 columns across, then copy down. Once you have copied down copy the new columns that have been added and in place do editpaste special as values. That way the new values become independent of the old values, finally if needed shuffle the new columns and cut and insert if you want the children's names next to the parents etc -- Regards, Peo Sjoblom "Cantor Lieberman" <Cantor wrote in message ... As Cantor of a large synagogue, I'd like to have membership data on my palm pilot. My office provided me with two files: 1. Includes ID, names, addresses, birthdates, phones...etc. 2. Includes ID, children's names and birthdates. Using the ID as the common link, how can I easily merge the correct children's names with parental records, creating one file which I can import to Outlook and thus sync with my palm. Appreciate your help! Robert |
#3
|
|||
|
|||
Peo
I was going to give the same answer but then I thought that it's likely that the children's data is on multiple rows i.e. ID1234 Child1 ID1234 Child2 ID5555 Child1 etc If this is the case then I was thinking the Vlookup would only find the first child Regards Rowan "Peo Sjoblom" wrote: Assume that you will use the table with most info (number 1 in your description) Assume also for simplicity that the first ID starts in A2 with a ID header in A1 (still in the 1st file) Now in the first empty adjacent column to the right in row 2 put =VLOOKUP($A2,[children.xls]Sheet1!$A$2:$E$200,COLUMN(B:B),0) where A2 holds the ID in the main table that will be looked up in the children table now copy the formula across as long as needed, if the children table is 4 columns across then copy 3 columns across, then copy down. Once you have copied down copy the new columns that have been added and in place do editpaste special as values. That way the new values become independent of the old values, finally if needed shuffle the new columns and cut and insert if you want the children's names next to the parents etc -- Regards, Peo Sjoblom "Cantor Lieberman" <Cantor wrote in message ... As Cantor of a large synagogue, I'd like to have membership data on my palm pilot. My office provided me with two files: 1. Includes ID, names, addresses, birthdates, phones...etc. 2. Includes ID, children's names and birthdates. Using the ID as the common link, how can I easily merge the correct children's names with parental records, creating one file which I can import to Outlook and thus sync with my palm. Appreciate your help! Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I transfer data between Excell sheets? | Excel Worksheet Functions | |||
Merging data in workbook | New Users to Excel | |||
Comparing sheets data | Excel Discussion (Misc queries) | |||
Summary of data from 20 sheets | Excel Discussion (Misc queries) | |||
sumif to add data in multiple sheets | Excel Worksheet Functions |