Sum of unique values with multiple conditions

[Tsjaikovski]

Hi,
I have the following data and would like something the happen in a worksheet_change event

ColA ColB Cost Total
1 A 200 1000
2 B 100 500
1 A 200 1000
3 A 50 1000
2 B 100 500
3 A 50 1000

Now I would like to have the following result

ColA ColB Cost Total Diff
1 AC 200 1000 750
2 AD 100 500 400
1 AC 200 1000 750
3 AC 50 1000 750
2 AD 100 500 400
3 AC 50 1000 750

where Diff is the difference between Total and Cost, but only for one unique value found in ColA. For the Value AC in COLB this means 1000-200+50 for the value AD in ColB this means 500-100

I hope this can be done easily.
Thx for helping me out.
Tsjai

 

[energman58]

Can you be clearer with what you expect the answer to be. In your example there are no unique column A values so I think there must be more to it than that

Also I cant see the logic behind:

"where Diff is the difference between Total and Cost, but only for one unique value found in ColA. For the Value AC in COLB this means 1000-200+50 for the value AD in ColB this means 500-100"

How do you get from A in the top row of your first example data to AC in the "after"? and then how do you decide what to subtract from total - I dont see where the 50 comes from

I many be being stupid of course!

 

[Tsjaikovski]

Hi,
Sorry for being not clear enough.
I also made a typing error in my initial data

this is how my initial data looks like:
Code Machine Product Cost Total
1 AC CAT_1 200 1000
2 AD CAT_3 100 500
1 AC CAT_2 200 1000
3 AC CAT_1 50 1000
2 AD CAT_2 100 500
3 AC CAT_4 50 1000

- As you can see every combination of column 'Code' and Column 'Machine' has the same value in the column "Cost". eg combination 1-AC gives 200, 3-AC gives 50,2-AD gives 100
- Every Machine has a value in the Column 'Total' that is identical (AC=1000,AD=500)
- Now I want to remove all the duplicate values in the Column 'Cost' given the condition 'Machine' and sum over the unique values:
For Machine AC this means the values 200,50 and the sum is 250
For Machine AD this means the value 100 and the sum is 100
- After this calculation is done for Each AC the sum of costs should be deducted from the Column 'Total'. For All AC Machine cases we would have a value 750 (1000-250), for all AD machines we would have a value 400 (500-100)

Code Machine Product Cost Total Diff
1 AC CAT_1 200 1000 750
2 AD CAT_3 100 500 400
1 AC CAT_2 200 1000 750
3 AC CAT_1 50 1000 750
2 AD CAT_2 100 500 100
3 AC CAT_4 50 1000 750

I would like to have a VBA solution so that I can use the code in a worksheet_change event.
I hope it's a bit clearer now.
Thx for helping me out,
Tsjai