2 argument Filter

poikl

Active Member
Joined
Jun 8, 2002
Messages
459
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?
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,159
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,452
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 8, 2002
Messages
459
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top