MATCH() and ISERROR() to extract set differences
 

If A and B are columnar arrays, and you want to subtract set B from set A, try this:

C1 =MATCH(A1,$B$1:$B$n,0)

This returns an error if the value of A1 does not appear anywhere in the range B1:Bn.

Then

D1 =IF(ISERROR(C1),A1,"")

Column D now contains only the values of A which are not found in B.


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng

MATCH() and ISERROR() to extract set differences
 

What is it with you guys?

This is a support forum, not a tips and tricks posting. Post this garbage to
your own website.

--
__________________________________
HTH

Bob

<Jim Fradkin wrote in message ...
If A and B are columnar arrays, and you want to subtract set B from set A,
try this:

C1 =MATCH(A1,$B$1:$B$n,0)

This returns an error if the value of A1 does not appear anywhere in the
range B1:Bn.

Then

D1 =IF(ISERROR(C1),A1,"")

Column D now contains only the values of A which are not found in B.


EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng