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

"kix" wrote:

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.

In cell C1 apply the below formula and copy/drag down as required...Once done
Copy ColCRight click PasteSpecialValues 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


--
Jacob


"kix" wrote:

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.

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,R OWS($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

"kix" wrote:

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.