Excel - Match Up/Merge Records

Asked By Kare on 16-Oct-07 11:21 AM
Using Excel 2003
I have two spreadsheets.  One contains 168 records and the other contains
376 records.

The spreadsheet with the 168 records includes the following columns:
Last Name
First Name
Middle Initial
ID Number

The spreadsheet with the 376 records includes the following columns:
ID Number
Record No.

I have to match up/merge the "ID Number" in both worksheets (both worksheets
are in the same workbook) and then delete the 208 records that do not match
up.  Hopefully the spreadsheets contain the same 168 records to match up.

How can this be done?  ANY help would be greatly appreciated, Karen




Dave Peterson replied on 16-Oct-07 12:39 PM
I would do this.

Create a new sheet.

Copy the ID data from both sheets into column A.  Leave a single header cell in
A1.

Use data|filter|advanced filter to get a list of unique IDs.  Put this unique
list in column B.  Look at Debra Dalgleish's site for Advanced filter with
unique records.
http://contextures.com/xladvfilter01.html#FilterUR

Delete column A (since column B has that unique list).

Then you can use a bunch of =vlookup() or =index(match()) formulas to return the
fields that you want.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html  (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html  (for =index(match()))



--

Dave Peterson