Filtering data for new residents

  • Thread starter Thread starter CPiper
  • Start date Start date
C

CPiper

I have a spreadsheet set up consisting of 5,000 mailing
addresses and names. Each month we receive a new list of
approximately 10,000 new names and addresses. I need to
be able to sort through the new addresses and return only
those names that are new to the 5,000 addresses that we
track.

Sheet1 contains my existing data and I am planning on
setting up sheet2 to contain the new data. Each sheet is
set up generally the same:

Column A: House Number
Column B: Street Name
Column C: Last Name
Column D: First Name


Is there any way to compare the Column A and B in both
sheets, match the addresses up and then check to see if
both of the names are the same? I know that this is an
involved question but I would appreciate someone pointing
me in the right direction.
 
Assuming both Sheets 1 & 2 have identical structure, with

Column A: House Number
Column B: Street Name
Column C: Last Name
Column D: First Name

Try this:

In Sheet1 (the 5,000 names & addresses that you track)

Put in E2: =TRIM(A2&"_"&B2&"_"&C2&"_"&D2)
Copy down to E5001

Name the range E2:E5001 as : MasterTable


In Sheet2 (the new list of 10,000 names & addresses)

Put in E2: =TRIM(A2&"_"&B2&"_"&C2&"_"&D2)
Copy down to E10001

Put in F2: =IF(ISNA(MATCH(E2,MasterTable,0)),"x","")
Copy down to F10001

Do a Data > Filter > Autofilter on col F
Select "x"

The names in the filtered rows will be
those names that are new to the 5,000 addresses
 
Back
Top