How to match between same numbers

G

ghost

Hi,
I have to columns, each one start from 1 to 500. the data is mixed (No. and
letters)
What I want to do is matching between the same data with a color or sort
them by each other and the unmatched date should be at the end of both
columns.
also, can those matched No. be color?
 
B

Bernie Deitrick

Let's say that you have column A and B on the same sheet.

Insert a new column B, then insert another column, so that your original data is in columns A and D.

In cell B2, use the formula

=ISERROR(MATCH(A2,D:D,FALSE))

and copy down to match your list in column A.

Then in cell E2, enter the formula

=ISERROR(MATCH(D2,A:A,FALSE))

and copy down.

Then sort columns A:B based on column B (the TRUE values are not matched). Then do the same for
columns D:E.

HTH,
Bernie
MS Excel MVP
 
Top