Merge Two Excel Files by Key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
(e-mail address removed) Y
(e-mail address removed) N

Example VERT file contents (field names in the first row)
Email_Address State
(e-mail address removed) IL
(e-mail address removed) 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
(e-mail address removed) Y -
(e-mail address removed) N IL
(e-mail address removed) - VA

Your help is very appreciated.
 
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.
 
Back
Top