2 argument Filter

[poikl]

Hi,Can you please help me with my Filter question?
I'm trying to filter an Inventory Excel Sheet with 20 Thousand items listed. Each Part # has its unique ID# (Stored in ColB). There are many source locations and each Source Location has a Loc ID(Alpha)# from Q-Z (This Loc# is stored in ColM). There can be the same part with many entries according to their individual source Locations but it can't be that the same Part# should be both, from Locations Q thru W and also from Locations X thru Z.
So for example Part # 2300 can show many rows of assorted Locations, Loc# Q thru Loc# W or it can show many rows from assorted Locations from Loc# X thru Z,
but it can't show from Loc# Q, R, S, T, U,V,W and also Loc# X, or, Y, or Z.
I need to filter out those "mistaken entries" in ColM where the Same Item # (in ColB) was entered from the 2 contradictory zones. Does this require a Function or is it a simple Filter?

 

[alansidman]

I am guessing that you will need a VBA solution to filter through. Suggest you post some sample data so that this can be developed. Employ the XL2BB function so that we don't have to try and recreate your data. Also please update your profile to indicate what version of XL you are using. This may make a difference in what solution is provided.

 

[jasonb75]

Crazy idea that wouldn't be practical but I'm going to post it anyway in the hope that it might inspire something more efficient.

Filter for values greater than zero on a helper column using the formula

=SUM(COUNTIFS(B:B,B2,M:M,{"Q","R","S","T","U","V","W"}))*SUM(COUNTIFS(B:B,B2,M:M,{"X","Y","Z"}))

With a smaller data set it would probably work, but given the data volume, I think it will be too much to process.

 

[poikl]

Hi Jason, Thank you very much. Even though it took quite a while to compute but it worked to display the problem entries. Exactly what I needed.