SOLVED

filtering multiple items

%3CLINGO-SUB%20id%3D%22lingo-sub-2662394%22%20slang%3D%22en-US%22%3Efiltering%20multiple%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662394%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20list%20of%20over%205%2C000%20Purchase%20order%20numbers.%20Each%20Purchase%20order%20can%20have%20a%20variety%20of%20different%20account%20codes%20attached%20to%20it.%20There%20are%2014%20account%20codes.%20I%20would%20like%20to%20Identify%20Purchase%20orders%20that%20only%20have%20one%20account%20code%20and%20that%20one%20%26nbsp%3Baccount%20code%20needs%20to%20be(6000%2C%206100%20or%206200).%20For%20Example%20in%20the%20table%20below%20%26nbsp%3BPurchase%20order%203%20and%207%26nbsp%3B%20would%20qualify%20as%20a%20purchase%20order%20with%201%20account%20code%20where%20the%20code%20equals%20one%206000%2C6100%20or%206200.%20I%20would%20also%20like%20the%20ability%20to%20Identify%20purchase%20orders%20that%20contain%20account%20code%20(6000%2C%206100%20or%206200)%20and%20any%20combination%20of%20the%20other%2011%20account%20codes.%20So%20for%20example%20Purchase%20order%201%2C2%20and%204%20are%20examples%20of%20purchase%20orders%20that%20contains%20account%20code%20(6000%2C6100%2C%20or%206200)%20and%20any%20combination%20of%20the%20other%2011%20accounts.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22275%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22123%22%3EPurchase%20Order%20No%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2274%22%3EAcct%20Cd%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2278%22%3ESum%20of%20Amt%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2100%3C%2FTD%3E%3CTD%3E9624.26%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2200%3C%2FTD%3E%3CTD%3E14209.52%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E9001.25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E6100%3C%2FTD%3E%3CTD%3E3624.93%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E2200%3C%2FTD%3E%3CTD%3E26953.98%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E6200%3C%2FTD%3E%3CTD%3E1750.5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E1425.67%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2200%3C%2FTD%3E%3CTD%3E35491.18%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E2400%3C%2FTD%3E%3CTD%3E2450.15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E40.5%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E2200%3C%2FTD%3E%3CTD%3E12901.04%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E2400%3C%2FTD%3E%3CTD%3E4593.94%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E2500%3C%2FTD%3E%3CTD%3E833.9%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E6000%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E2400%3C%2FTD%3E%3CTD%3E486.39%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2662394%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2662514%22%20slang%3D%22en-US%22%3ERe%3A%20filtering%20multiple%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813299%22%20target%3D%22_blank%22%3E%40dw700d%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665053%22%20slang%3D%22en-US%22%3ERe%3A%20filtering%20multiple%20items%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665053%22%20slang%3D%22en-US%22%3EThank%20you%20Hans%20this%20helps%20a%20great%20deal.%20The%20accounts%20codes%20are%20actually%206121.219M%2C%206231.167M%20%2C6722.99%2C6726.99.%20I%20am%20experiencing%20challenges%20trying%20to%20replicate%20your%20formula%20for%20type%202.%20Are%20you%20able%20to%20rewrite%20the%20formula%20for%20me%20with%20the%20account%20codes%20below%3CBR%20%2F%3E6121.219M%3CBR%20%2F%3E6231.167M%3CBR%20%2F%3E6722.99%3CBR%20%2F%3E6726.99%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20help%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a list of over 5,000 Purchase order numbers. Each Purchase order can have a variety of different account codes attached to it. There are 14 account codes. I would like to Identify Purchase orders that only have one account code and that one  account code needs to be(6000, 6100 or 6200). For Example in the table below  Purchase order 3 and 7  would qualify as a purchase order with 1 account code where the code equals one 6000,6100 or 6200. I would also like the ability to Identify purchase orders that contain account code (6000, 6100 or 6200) and any combination of the other 11 account codes. So for example Purchase order 1,2 and 4 are examples of purchase orders that contains account code (6000,6100, or 6200) and any combination of the other 11 accounts.

 

 

Purchase Order No     Acct Cd               Sum of Amt
121009624.26
1220014209.52
160009001.25
261003624.93
2220026953.98
362001750.5
460001425.67
4220035491.18
424002450.15
5600040.5
6220012901.04
624004593.94
62500833.9
76000200
82400486.39

 

 

 

 

5 Replies
best response confirmed by dw700d (Occasional Contributor)
Solution

@dw700d 

See the attached sample workbook.

 

S0689.png

Thank you Hans this helps a great deal. The accounts codes are actually 6121.219M, 6231.167M ,6722.99,6726.99. I am experiencing challenges trying to replicate your formula for type 2. Are you able to rewrite the formula for me with the account codes below
6121.219M
6231.167M
6722.99
6726.99

Thank you for your help

@dw700d 

Are the latter two text values or number values?

all four values are considered text

@dw700d 

Thank you. The formula in D2 becomes

 

=AND(COUNTIF($A$2:$A$16,$A2)=1,OR(B2={"6121.219M","6231.167M","6722.99","6726.99"}))

 

and that in E2:

 

=SUM(COUNTIFS($A$2:$A$16,$A2,$B$2:$B$16,{"6121.219M","6231.167M","6722.99","6726.99"}))*COUNTIFS($A$2:$A$16,$A2,$B$2:$B$16,"<>6121.219M",$B$2:$B$16,"<>6231.167M",$B$2:$B$16,"<>6722.99",$B$2:$B$16,"<>6726.99")>0