Matching data in two spreadsheets

  • Thread starter Barry in Denver
  • Start date
B

Barry in Denver

I have 2 spreadsheets. In spreadsheet A I have a column of e-mail addresses
500 names long. In spreadsheet B, I have these same e-mail addresses plus
800 more all randomly listed in a column. I want to sort the long B list
such that the e-mails that match the short A list are noted or the
non-matches are deleted or whatever is appropriate.
I'm using Excel for the Mac v X
 
M

Max

One way ..

Assume the data is in col A, A1 down in both sheets A and B
And Sheet A is the "master" reference, so as to speak

In sheet: B
------------
Put in B1: =IF(A1="","",IF(MATCH(A1,A!A:A,0),"",ROW()))

Put in C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

Select B1:C1, fill down until the last row of data in col A

Col C will return the list of items which match with the master in sheet A
 
M

Max

Typo, sorry ..

Line
Col C will return the list of items which match with the master in sheet A
Click to expand...

should read:
Col C will return the list of items which do not match with the master in
Click to expand...
sheet A

If you want the list of items which match with the master in sheet A,
just change the line:
Put in B1: =IF(A1="","",IF(MATCH(A1,A!A:A,0),"",ROW()))
Click to expand...
to
Put in B1: =IF(A1="","",IF(MATCH(A1,A!A:A,0),ROW(),""))
 
Top