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))),"")
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))),"")