is this search possible?

**Meebell** · 07-20-2007, 08:23 AM

I have a list of 2000 sku's in column a and a list of about 3000 sku's in column b. I need to know what numbers are in column a that are not in column b, but there are lots of duplicates within each column.

Is it possible to show the numbers in column a that are not in column b WITHOUT taking into account the duplicates already in column a?

**oldchippy** · 07-20-2007, 08:36 AM

sku's???

Try this, with your longer list in column A and the shorter list in column B, put this in C1

=IF(COUNTIF($B$1:$B$181,A1)=0,A1,"") then press Ctrl+Shift+Enter then drag down column C, this will show
listed in A and NOT in B

**daddylonglegs** · 07-20-2007, 08:52 AM

You could use conditional formatting to colour the first instance only of any SKU appearing in column A that doesn't appear in column B

Select column A

Format > conditional formatting > "formula is" option with formula

=ISNA(MATCH(A1,B:B,0))*(COUNTIF(A$1:A1,A1)=1)

select desired colour pattern, OK

or you could use a similar formula on the worksheet, in C1 copied down

=IF(ISNA(MATCH(A1,B:B,0))*(COUNTIF(A$1:A1,A1)=1), A1,"")