Logo 
Search:

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: 934
  

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: 

 

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
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
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
=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: