Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge Two Excel Files by Key
Wow, I've read and read but just cannot find a straight-forward answer to a
pretty simple scenario. :-( I have two Excel 2003 files (one called ETAP, one called VERT), each has as its first column Email_Address. Some records in ETAP match those found in VERT, using Email_Address as the key. There are some records in ETAP that are not in VERT, and vice versa. Each file can contain several thousand unique records. Example ETAP file contents (field names in the first row) Email_Address MemberCode Y N Example VERT file contents (field names in the first row) Email_Address State IL VA I'd like the resulting file to include all columns from both files. Example RESULTING file contents (field names in the first row, in no particular order, with a dash indicating missing field values). Email_Address MemberCode State Y - N IL - VA Your help is very appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merge Two Excel Files by Key
Here's one quick set up using non-array formulas
which should yield the required results .. A sample construct is available at: http://www.savefile.com/files/357128 Merge 2 shts by key col.xls (nicely rendered with full details) I'll presume the 2 source data are in sheets named: ETAP & VERT, within cols A and B, data from row2 down In a new sheet: Resulting, Just do a manual copy & paste over of the email addresses from ETAP & VERT into A2 down, one paste below the other in sequence. Paste the col labels in C1:E1, viz.: Email_Address, MemberCode, State Then place In B2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)1,"",ROW())) (Leave B1 blank) In C2: =IF(ROW(A1)COUNT($B:$B),"",INDEX(A:A,SMALL($B:$B, ROW(A1)))) In D2: =IF(C2="","",IF(ISNA(MATCH(C2,ETAP!A:A,0)),"-",INDEX(ETAP!B:B,MATCH(C2,ETAP!A:A,0)))) In E2: =IF(C2="","",IF(ISNA(MATCH(C2,VERT!A:A,0)),"-",INDEX(VERT!B:B,MATCH(C2,VERT!A:A,0)))) Select B2:E2, copy down to the last row of data in col A. Cols C to E will return the desired results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "THW" wrote: Wow, I've read and read but just cannot find a straight-forward answer to a pretty simple scenario. :-( I have two Excel 2003 files (one called ETAP, one called VERT), each has as its first column Email_Address. Some records in ETAP match those found in VERT, using Email_Address as the key. There are some records in ETAP that are not in VERT, and vice versa. Each file can contain several thousand unique records. Example ETAP file contents (field names in the first row) Email_Address MemberCode Y N Example VERT file contents (field names in the first row) Email_Address State IL VA I'd like the resulting file to include all columns from both files. Example RESULTING file contents (field names in the first row, in no particular order, with a dash indicating missing field values). Email_Address MemberCode State Y - N IL - VA Your help is very appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
I need to merge into an Excel Main Document with Excel Data-How? | Excel Discussion (Misc queries) | |||
How can I merge multiple sheets from different Excel files workbo. | Excel Worksheet Functions | |||
merge data from 2 Excel files | Excel Worksheet Functions | |||
how i can merge more than two excel files in one file. | New Users to Excel |