Excluding in Excel

[Kuba]

Hello
I have a question for Excel knowers

I have a list of items in column A
I also have a list of items in column B
Some of the items in A and B are duplicates
I'd like to obtain a C column sustaing of items that appear in column A but
NOT appear in column B

Can you help me solving my problem?
Please mail me on priv playa(at)poczta.fm

Kind Regards,
Jakub Lenczowski
Krakow, Poland

 

[Max]

One way, using non-array formulas ..

Assuming data in cols A and B, from row1 down

Put in C1:
=IF(ISERROR(SMALL(D,ROW())),"",
INDEX(A:A,MATCH(SMALL(D,ROW()),D,0)))

Put in D1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW()))

Select C11, copy down to last row of data in col A

Col C will return the required results*, all neatly bunched at the top
*Items in col A not found in col B

 

[Harlan Grove]

Max wrote...

One way, using non-array formulas .. ....
Put in C1:
=IF(ISERROR(SMALL(D,ROW())),"",
INDEX(A:A,MATCH(SMALL(D,ROW()),D,0)))

Put in D1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),"",ROW()))

....

Unless this needs to be dynamic, it'd be easier to use an advanced
filter. That'd require dummy field names in the row immediately above
the data ranges in cols A-C, with the col C's field name the same as
col A's. It also requires a 2-row by 1-col criteria range with the top
cell blank and the bottom one containing the formula
=COUNTIF(B:B,A2)=0. Select A:B, run Data > Filter > Advanced Filter,
choose to copy the results to another range, select C1 as the copy to
range, set the criteria range and click OK.

As for formulas, you could reduce your col D formulas to

D1:
=IF(COUNT(1/ISNA(MATCH(A1,B:B,0))/(A1<>"")),ROW(),"")

and your col C formulas to

C1:
=IF(COUNT(D)>=ROWS(C$1:C1),INDEX(A:A,SMALL(D,ROWS(C$1:C1))),"")

 

[Max]

.. you could reduce your col D formulas to
D1:
=IF(COUNT(1/ISNA(MATCH(A1,B:B,0))/(A1<>"")),ROW(),"")
and your col C formulas to
C1:
=IF(COUNT(D)>=ROWS(C$1:C1),INDEX(A:A,SMALL(D,ROWS(C$1:C1))),"")

Thanks for the formula refinements, Harlan.
Appreciated ..