Matching data in two spreadsheets
 

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

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Barry in Denver" <Barry in wrote in
message ...
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

Typo, sorry ..

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

should read:
Col C will return the list of items which do not match with the master in
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()))
to
Put in B1: =IF(A1="","",IF(MATCH(A1,A!A:A,0),ROW(),""))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----