Aug 18 2021 12:51 PM
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 |
1 | 2100 | 9624.26 |
1 | 2200 | 14209.52 |
1 | 6000 | 9001.25 |
2 | 6100 | 3624.93 |
2 | 2200 | 26953.98 |
3 | 6200 | 1750.5 |
4 | 6000 | 1425.67 |
4 | 2200 | 35491.18 |
4 | 2400 | 2450.15 |
5 | 6000 | 40.5 |
6 | 2200 | 12901.04 |
6 | 2400 | 4593.94 |
6 | 2500 | 833.9 |
7 | 6000 | 200 |
8 | 2400 | 486.39 |
Aug 18 2021 01:08 PM - edited Aug 18 2021 01:09 PM
SolutionAug 19 2021 06:12 AM
Aug 19 2021 06:23 AM
Are the latter two text values or number values?
Aug 19 2021 06:37 AM
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
Aug 18 2021 01:08 PM - edited Aug 18 2021 01:09 PM
Solution