Working on segments of data from within a list

[Gordon]

I have a list of data which has the capacity to be say 5000 lines in length.
The simplified structure looks as follows:



A B C
D
RefCode Price 1 Price2
Required

1 11111 200 20
=200/380*20
2 11111 200 60
=200/380*60
3 11111 200 100
=200/380*100
4 11111 200 200
=200/380*200
5 22222 100 20
6 22222 100 100
7 33333 500 500
=500/700*500
8 33333 500 200
=500/700*200
9 44444 150 150
10 44444 150 10
11 44444 150 20
12 44444 150 30
13 44444 150 150

The price in column B will always be the same for each entry in column A.

What I would like to be able to do (without inserting a manual subtotal
calculation under each RefCode segment) is to take the Price1 figure in
column B for the Refcode grouping and divide it by the sum of the Price2
figures in column C to give a redistribution of the total price against the
Price2 figures( Please see column D)

I’m probably straying into the realms of using a bit of VB coding to
accomplish this which is where I’m somewhat lost but if anyone has any ideas
as to how I might automate this I would be very interested to hear.

Thank you

Gordon.

 

[Jacob Skaria]

Try this formula in D1 and copy down..

=B1/SUMIF(B:B,B1,C:C)*C1

If this post helps click Yes