delete unwanted IDs

[melvyndb]

Hi all,

I need help badly, this is causing me sleepless nights.

my IDs are not in order, so might need to look in the range rather than row by row. (or can autofilter them in ascending order)

IDstest.zip

but my main objective is to find out the IDs NOT in AID from BID. (i.e. if AID do not have 111 and BID does, get rid of 111)

so am not sure if 1 code will do and just drag down, but thats the idea.

not necessary to delete, could just make a marking like 'x' in a new column also works fine.

my idea is,
Row 1=if(BID1 <> AID1:600, then delete)
Row 2=if(BID2 <> AID1:600, then delete)
drag down

hope for some help here, thanks.

regards,
Melvyn

[Fotis1991]

Hi

Im C2, and copy down.

=IF(COUNTIF($A$2:$A$600;B2)=0;"x";"")

Change the semi-colons to comma if you have to do this.

Is this, helps you?

[dilipandey]

Hi Melvyn,

Use below forumla in column C and drag it down:-

=IF(ISNUMBER(MATCH($B2,A:A,0)),"","x")

Regards,
DILIPandey

<click on below 'star' if this helps>

[npamcpp]

Hi,

enter this formula into cell C2:

=IF(ISNA(MATCH(A2,B:B,0)),"",A2)

Copy down to row 538. Then add a header in C1 and sort by that column. The result is the list of codes that are in column A AND in column B.

See attached file.

regards,

[melvyndb]

Guys,

Thank you, it helped. Appreciate your kindness. Up reputation.