Difference in Two Columns

[toongal12]

I have two columns, A and B. A is a list of 100 items. B is a smaller list with some of the same items.


I want to be able to output one list that returns the A list, minus what was in B list.


Example:


Column a:
Cat
Dog
Bird
Lion
Bear


Column B:
Cat
Bear




So now to minus what Column B has from Column A
Column C:
Dog
Bird
Lion


I was able to use an IF statement, but returns empty cells for the different ones. I need a constant drop down list without empty cells.
=IF(ISERROR(MATCH(A1,$B$1:$B$100,0)),A1,"")




I can't figure out how to do it with subtracting lists. I feel like I am on the right track but still fails.


=IFERROR(INDEX(A$1:A$100,SMALL(IF(NOT($A1=$B$1:$B$100),ROW(A$1:A$100)-ROW(A$1)+1),ROW(C$1:C1))),"")

 

[Eric W]

Consider:

	A	B	C
1	List 1	List 2	Difference
2	Cat	Cat	Dog
3	Dog	Bear	Bird
4	Bird		Lion
5	Lion
6	Bear
7
8
9
10

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Array Formulas Cell Formula C2 {=IF(C1="","",IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(ISNA(MATCH($A$2:$A$10,$B$2:$B$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS($C$2:$C2))),""))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The heading in C1 is required for this version.

 

[toongal12]

That's what it was! The C1 part isn't necessary

 

[Eric W]

True, you can just use:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF($A$2:$A$10<>"",IF(ISNA(MATCH($A$2:$A$10,$B$2:$B$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1)),ROWS($C$2:$C2))),"")

but I often add the IF(C1="" part to minimize the amount of calculations Excel has to do for the blank cells. It just depends on how many formulas you have in your spreadsheet if it matters or not.

But in any case, I'm glad you got what you needed!

 

[toongal12]

If I drag it over to bring other information from other columns, it shows data from the cells that were removed.

 

[toongal12]

I think it was just wrong placed absolutes. Seems to be okay now!

 

[L. Howard]

Edit deleted