Extract unique values

[Jill]

Hello Everyone,

This is a repost from yesterday. I'm trying, by use of a
single formula, to extract values from col A that do not
appear in col B. The data looks like this -

Jim Tom
Tom Bob
Eric Sue
Sam Jim
Jill Bill
Sue Eric

The output in col C would look like this -

Sam
Jill

I can do this in two steps using a helper col with a
formula and then using another fromula in the target col.
I would like to know how to do this in one step using just
a single formula.

I can generate this array with the helper formula -

#N/A
#N/A
#N/A
4
5
#N/A

But I can not figure a way to pass that array in a formula
like this -

=INDEX(A2:A7,SMALL(IF(ISNUMBER(MATCH .......

Like I said, I can do this using 2 formulas and the helper
col but would like to be able to do it in one formula
without the need for a helper.

Any ideas?

Thanks
Jill

 

[Frank Kabel]

Hi Jill
put the following array formula (entered with CTRL+sHIFT+ENTER) in C1:
=INDEX($A$1:$A$10,SMALL(IF(COUNTIF($B$1:$B$10,$A$1:$A$10)=0,ROW($A$1:$A
$10)),ROW(1:1)))
and copy down

 

[Jill]

Thanks Frank!

-----Original Message-----
Hi Jill
put the following array formula (entered with CTRL+sHIFT+ENTER) in C1:
=INDEX($A$1:$A$10,SMALL(IF(COUNTIF($B$1:$B$10,$A$1:$A$10) =0,ROW($A$1:$A
$10)),ROW(1:1)))
and copy down

--
Regards
Frank Kabel
Frankfurt, Germany



.