Compare 2 Lists, Create list of Unique Values

[mgirvin]

Dear Smartest Excelers in The World,

I have List1 in column A starting in cell A1:

List1
Sioux
Red
Chin
Phil
Abe


I have List2 in column C starting in cell C1:

List2
Sioux
Red
Sue
Chin
Phil
Abe
Mo

I am trying to create a third list in Column E starting in cell E1 that will extract the values in List2 that are not in List1. I want it to look like this:

Unique Records from List2 that are not in List1
Sue
Mo



Is there a way to put a formula in column E that will do this automatically? Although I am primarily interested in a formula, I might secondarily be interested in some VBA code.

[Latchmaker]

Try this VBA
Sub CreateUniqueList()

Range("D2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3],RC[-1])"
Selection.AutoFill Destination:=Range("D2:D8"), Type:=xlFillDefault
Range("D2:D10000").Select
Range("C1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="0"
Columns("C:C").Select
Selection.Copy
Range("E1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFilter
Columns("D:D").Select
Selection.ClearContents
Range("E1").Select
ActiveCell.FormulaR1C1 = "List3"
Range("E1").Select
End Sub

[mgirvin]

Dear LatchMaker,

Thanks for your code! It works fine! Similarly, I have created a Macro but I used Advanced Filter and a True/False formula in the Macro.

What I am really after is a formula that I can put in the E column that will do this dynamically.

Any Formula idea, Excel Masters?

[Aladin Akyurek]

List1		List2		2
Sioux		Sioux		Items List2 Not In List1
Red		Red		Sue
Chin		Sue		Mo
Phil		Chin
Abe		Phil
		Abe
		Mo

E1:

=SUMPRODUCT(1-ISNUMBER(MATCH(C2:C8,A2:A6,0)))

E2:

Control+shift+enter, not just enter...

=IF(ROWS($E$3:E3)<=$E$1,INDEX($C$2:$C$8,
   SMALL(IF(1-ISNUMBER(MATCH($C$2:$C$8,$A$2:$A$6,0)),
    ROW($C$2:$C$8)-ROW($C$2)+1),ROWS($E$3:E3))),"")

and copy down.

[mgirvin]

Dear Aladin,

Again, you come to the rescue!

I have been working on this for a few hours and I just came up with this formula in Column E for the set up as I described in the first post:

{=IF(ROWS(E$2:E2)<=COUNTA($C$2:$C$8)-COUNTA($A$2:$A$6),INDEX($C$2:$C$8,SMALL(IF(ISNA(MATCH($C$2:$C$8,OFFSET($A$2,0,0,COUNTA($C$2:$C$8)),0)),ROW($C$2:$C$8)-ROW($C$2)+1),ROWS(E$2:E2))),"")}

Now, I will go and try your formula!

Thanks!!!

[mgirvin]

Dear Aladin,

As you can see from my formula in my above post, I used all the tricks I learned from the last solution you posted for me under the subject line "Reverse two-way lookup".

What I learned from you this time is an elegant method for an ISFALSE array:

1-ISNUMBER(MATCH($C$2:$C$8,$A$2:$A$6,0))

Totally awesome!

Thanks!