Clean Up Inventory Workbook - Compare 2 Columns & Filter Out Data

[fischXcell2]

Hello Everyone.
Although I have been using Excel for years and years, I am only just learning to use formulas, functions and VBA.

There are two columns of inventory numbers. Column A has items that need to be destroyed. Column B has items that have been destroyed.

I want to clean up Column A by removing all the inventory numbers that are in Column B. In other words, if the inventory number exists in Column B, it must be removed from Column A.

Is there a function or combination of functions that will accomplish this? It would be awesome if it could be a dynamic function.
I tried using Filter and Filter with If, but I am just not skilled enough to figure it out.

 

[Vogateer]

Something like this?

=FILTER(A2:A100, NOT(ISNUMBER(MATCH(A2:100,B2:100,0))), "")

 

[Cubist]

@Vogateer
You're missing a few letters for the cell reference.

=FILTER(A2:A100, NOT(ISNUMBER(MATCH(A2:A100,B2:B100,0))), "")

You can simplify with ISERROR().

=FILTER(A2:A100, ISERROR(MATCH(A2:A100,B2:B100,0)))

 

[Vogateer]

@Vogateer
You're missing a few letters for the cell reference.

=FILTER(A2:A100, NOT(ISNUMBER(MATCH(A2:A100,B2:B100,0))), "")

You can simplify with ISERROR().

=FILTER(A2:A100, ISERROR(MATCH(A2:A100,B2:B100,0)))[/CODE
[/QUOTE]

Yes, good catch.

 

[fischXcell2]

Yes, good catch.

Wow! Thank you, guys!! I tried it and except for some zeros in the last few cells, this works perfectly.

 

[Cubist]

You're welcome. @Vogateer did most of the heavy lifting.

 

[fischXcell2]

You're welcome. @Vogateer did most of the heavy lifting.

So, can you guys help at all with the zeros? I attached a screenshot. It's not really all that important, but I wouldn't mind having a better understanding of the formula and why it's returning the zeros.

 

[fischXcell2]

So, can you guys help at all with the zeros? I attached a screenshot. It's not really all that important, but I wouldn't mind having a better understanding of the formula and why it's returning the zeros.

Never mind!! I fixed it....I changed zu verschotten to a Named Range and the zeros disappeared. Here's the formula that I used:

=SORT(FILTER(zu_verschrotten,ISERROR(MATCH(zu_verschrotten,O3:O99,0)),""),1,1)