Hello, I am having trouble sorting and subtotaling based on types with various difference?
source | ||||||||||||||
P# | NAME | Product | Code | Dcode | PurchCode | C % M | P % M | Vol | Val | Type | ||||
500 | Sam | Grapes | UNIT | SP | Smith | 0.5 | 0.5 | 0.5 | 0.5 | C | ||||
500 | Sam | Grapes | MV | SP | Smith | 0.5 | 0.5 | 0.3 | 0.3 | P | ||||
500 | Sam | Grapes | MV | SP | Smith | 0.5 | 0.5 | 0.2 | 0.2 | P | ||||
500 | Sam | Grapes | UNIT | SR | Smith | 0.5 | 0.5 | 0.5 | 0.5 | C | ||||
500 | Sam | Grapes | MV | SR | Smith | 0.5 | 0.5 | 0.3 | 0.3 | P | ||||
500 | Sam | Grapes | MV | SR | Smith | 0.5 | 0.5 | 0.2 | 0.2 | P | ||||
500 | Sam | Pear | UNIT | SR | Smith | 0.5 | 0.5 | 0.75 | 0.75 | C | ||||
500 | Sam | Pear | MV | SR | Smith | 0.5 | 0.5 | 0.21 | 0.21 | P | ||||
500 | Sam | Pear | MV | SR | Smith | 0.5 | 0.5 | 0.04 | 0.04 | P | ||||
desired outcome | ||||||||||||||
P# | NAME | Product | Code | Dcode | PurchCode | C % M | P % M | Vol | Val | Type | VAR1 | VAR2 | RESULT | |
500 | Sam | Grapes | UNIT | SP | Smith | 0.5 | 0.5 | 0.5 | C | 0 | 0 | |||
500 | Sam | Grapes | MV | SP | Smith | 0.5 | 0.5 | 0.5 | P | 0 | 0 | |||
500 | Sam | Grapes | UNIT | SR | Smith | 0.5 | 0.5 | 0.5 | C | 0 | 0 | |||
500 | Sam | Grapes | MV | SR | Smith | 0.5 | 0.5 | 0.5 | P | 0 | 0 | |||
500 | Sam | Pear | UNIT | SR | Smith | 0.5 | 0.75 | 0.75 | C | -0.25 | -0.25 | FLAG | ||
500 | Sam | Pear | MV | SR | Smith | 0.5 | 0.25 | 0.25 | P | 0.25 | 0.25 | FLAG |
Solved! Go to Solution.
Hi @nihou,
Please see the attached workflow. The major part of the solution is using the summarize tool create your groupings then calculating var1 & 2 with a formula tool. The output order is a little bit different but gets the same results.
I hope this helps!
@ConnorK Thank you! That worked!