A specific set algebra question, and how to solve it in Excel, has been bugging me for years. Literally. I’ve asked a bunch of people, and the more advanced Excel warriors have given partial answers.
All the answers that get close involve many clicks, and usually manual pivot tables. Those kind of work but I dislike solutions with so many clicks.
So I finally asked on Yahoo how one does a particular type of set algebra operation in Excel: inspect column A for all values not in column B, and output those values into column C. Not very complicated.
Chaminda came through right away with a great answer that works for me. Check it out on Yahoo Answers at http://answers.yahoo.com/question/index?qid=20090427145458AAQtqDy.
To repeat the core part:
Put in C1 & drag down to C10
=IF(ISERROR(MATCH (A1,$B$1:$B$10,0)),A1,””)
=IF(ISERROR(MATCH (A1,$B$1:$B$10,0)),A1,””)
If you need more info, add details or email me.
ADDED- And if you do not want any empty cells in between, try this.
=IFERROR(INDEX($A$1:$A$10, SMALL(IF(NOT (IFERROR(MATCH ($A$1:$A$10,$B$1:$B$10,0),0)), ROW($A$1:$A$10)),ROW())),””)
Yoav Shapira's Personal Blog 