Here is my basic problem. An Item can have multiple rows if it has multiple Cost Types.
I want to setup a formula that finds those rose that have multiple Cost Types and subtract A from B and replace the amount for A with the result or create a new row (call it A-B) and get rid of the original A and B rows for an item. I want to keep all As that don't have a corresponding B and all Cs.
What would the formula (or multi-field formula) look like?
Thank you for your assistance.
Item | Cost Type | Amount |
99999 | A | 235253 |
10001 | A | 32423 |
10001 | B | 243523 |
10001 | C | 3235 |
54321 | A | 154325 |
Solved! Go to Solution.
Thank you @john_miller9. This almost works. But, it still keeps the original A and B rows for Item 10001 (and all Items like 10001 when I try to scale it).
This works @danrh great. But, I quickly ran into a problem. My real dataset also has a column for Quantity. Where Cost Type A will always have a value between 0 and N. Cost Type B and C will always be 0. Adding Quantity, trips up the approach. But, I am working amending the Quantity column back to the final result of your solution.
It worked. Thank you.
I had to add a quantity field. I filtered out Quantity, solved for A-B, and appended Quantity to the result.