Search:

sort unmatched cells

Asked By: Eva    Date: Dec 02    Category: MS Office    Views: 1743

I have 2 columns with over 30000 records in column A and 23889 in B

I would like to sort and match A and B and exclude the ones that do
not match.

Any help???

Share:

1. Insert a new column between A & B (new column B; old column B becomes C)
2. In first row of data (row 1 in my example), enter Vlookup formula in column
B.
B1 =VLOOKUP(A1,C:C,1,FALSE)
3. Copy formula down for as many rows as have data in column A.
4. In first row of data (1), enter Vlookup formula in column D.
D1 =VLOOKUP(C1,A:A,1,FALSE)
5. Copy formula down for as many rows as have data in column C.
The cells  in columns  B & D which returned #N/A have no matching value.
6. Convert columns B & D to values (Copy, then Paste Special as Values)
7. Select columns A & B. Sort by column B first and A second (using Sort from
8. Copy the cells in columns A & B where B contains #N/A. Paste them on
another sheet if you want to keep a list of them. Delete the contents of those
cells (not rows) on your original sheet.
9. Repeat steps 7 & 8 for columns C & D.
10. Delete columns B & D.
Columns A & B should now have matching values in all rows. Records with no
match are on the other sheet (if you copied & pasted them there).

Enter following formula in C1
=if(countif(A\$1:A\$30000,C1)>1,"y","n")
Copy the same uuto C30000
Select c1:c30000
Select copy
Select paste special
Select values
Sort entire data on column c

Didn't find what you were looking for? Find more on sort unmatched cells Or get search suggestion and latest updates.

Tagged: