Formula to sort

  • Thread starter Thread starter kix
  • Start date Start date
K

kix

I have two columns 1st column has certain account numbers, the 2nd has those
same numbers plus a couple hundred more. I want to eliminate the account
numbers in 2nd column that do not exist in 1st column. I don't know how to
write a
formula for this.

Thank you.
 
by Advanced filter
For Excel 2003, go Data->Filter->Advanced Filters
List range is your 2nd column, Criteria range is 1st column
Check unique records only will show 1 result of each account

by formula
At new column, type =IF(ISERROR(VLOOKUP(B10,$A:$A,1,FALSE)),"Not
Exist","Exist")
Drag down the formula.

Hope it helps.
eksh
 
In cell C1 apply the below formula and copy/drag down as required...Once done
Copy ColC>Right click> PasteSpecial>Values to convert formulas to
values...Sort data wrt ColC and delete the records in ColB for which ColC is
blank

=IF(COUNTIF(A:A,B1),"Exist","")

Col A Col B Col C
1011 1011 Exist
1012 1015 Exist
1013 1002
1015 1005
1013 Exist
1015 Exist
 
Assuming lists are in column A & B, try this for a list of a/c nos. compiled
from B that also appear in column A.
Assume row 1 has headings then in C2 enter:
=IF(A2="","",IF(ISNUMBER(MATCH(A2,B:B,0)),"",ROW()))
in D2 enter:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
Copy both C & D down to the bottom of your list.
Hide column C and D will contain the list you require.
Hope this helps
 
Back
Top