MATCH() and ISERROR() to extract set differences

J

Jim Fradkin

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
 
B

Bob Phillips

What is it with you guys?

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Condense ISERROR(VLOOKUP(MATCH())) to VBA function. 3
INDEX, MATCH, AND OFFSET... OH MY! 4
find largest column width and ... 4
Triming trailing spaces before using Match on columns of numbers withleading zeroes formatted as tex 1
Problem using Match on columns of numbers with leading zeroesformatted as text ??? 4
Determine if filter set on specific column 3
Calculate 'exact' age in EXCEL 5
Display alert if data exists 2

Top