Subtract one row from another based on a common ID and different cost categories

[hellyars]

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

[john_miller9]

Here's one way to solve this

[derekbelyea · Accepted solution]

Here is an alternative approach to solving this.

[danrh · Accepted solution]

This should get you there:

The Cross Tab puts the different cost types in different columns.  Then a simple formula produces the result you're looking for, a Transpose to put the data back in it's original form, and a filter to get rid of the excess rows.

Hope it helps!

[hellyars]

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).

[hellyars]

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.  

[hellyars]

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.