MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

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???



2 Answers Found

Answer #1    Answered By: Daniel Jones     Answered On: Dec 02

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
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.
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
the Data menu).
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).

Answer #2    Answered By: Mercedes Andrews     Answered On: Dec 02

May this help you
Enter following formula in C1
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.