Try this play ..

Assume data as posted in cols A and B, from row2 down
(you had a typo for 3545 in col B, it should read as 3345, I believe)

First, insert 2 new cols C & D

Place in C2: =IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),A2,""))
Copy C2 down to the last row of data in col A
This returns the desired results
(Col A found in col B)

Place in D2: =IF(B2="","",IF(ISNUMBER(MATCH(B2,A:A,0)),"",B2))
Copy D2 down to the last row of data in col B
This should return acceptable "converse" results
(Col B NOT found in col A)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:370 Subscribers:68
xdemechanik
---
"Eilean" wrote:
Ive got two columns with staff ID numbers which Id like to firstly match
and then sort so that identical ID numbers are on the same row. For example,
unsorted the columns look like this:
A B
3345 3876
3654 3980
2872 3545
3980 2872

Id like to have the ID numbers matched and sorted by row like this:
A B
3345 3345
3654
2872 2872
3980 3980
3876

Column A has about 3000 entries and Column B has about 400 entries, so that
not all ID numbers in Column B appear in Column A. Columns D, E, F etc have
other information such as the persons name, telephone number, email address
etc. How do I do this? I am a very basic Excel user so Im not very familiar
with formulas etc. I'm using Excel 2007