Match Up/Merge Records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 16 Oct 2007 16:21 GMT

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

Reply to this Message

Dave Peterson - 16 Oct 2007 17:39 GMT

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()))

> Using Excel 2003
> I have two spreadsheets. One contains 168 records and the other contains
[quoted text clipped - 15 lines]
>
> How can this be done? ANY help would be greatly appreciated, Karen

Signature

Dave Peterson

Reply to this Message