Carrying out calculations on segments of data from within a workbo

[Gordon]

I have a list of data - which has the potential to be up to 10,000 rows in
length.
The structure of the date is as follows (Although fairly simplified): Each
Ref No set of rows has the capacity to be from 2 to 14 rows in size.
A B C
Ref NO Price 1 Price 2
1 11111 4000 250
2 11111 4000 4000
3 11111 4000 3000
4 22222 3000 1200
4 22222 3000 3000
5 33333 2500 450
6 33333 2500 2500
7 44444 4500 4500
8 44444 4500 3000
9 44444 4500 200
10 44444 4500 2500
11 44444 4500 200

What I would like to be able to do with some sort of drag and drop or VB
coding without carrying out an individual manipulation of each Ref No
Grouping is to take the Price 1 amount for each Ref No (Which will always be
the same on each row for that reference) and apportion it in a percentage
commensurate with the sum and individual parts of the Price 2 row figures:

Ie for the 44444 set of data: i would like to be able to take 4500, divide
it by 10,400,( the sum of the price codes accociated with 44444 in column C)
and then create a new column which multiplies that answer out by column C's
individual price:

A B C New Column
7 44444 4500 4500 4500/10400 *4500
8 44444 4500 3000 4500/10400 *3000
9 44444 4500 200 4500/10400 *200
10 44444 4500 2500 4500/10400 *2500
11 44444 4500 200 4500/10400 *200


Any Ideas would be most welcome

Many Thanks

Gordon

 

[Jacob Skaria]

Try the below formula

Col A Col B Col C Col D
Ref NO Price 1 Price 2
11111 4000 250 =B2/SUMIF(B:B,B2,C:C)*C2
11111 4000 4000
11111 4000 3000
22222 3000 1200
22222 3000 3000
33333 2500 450
33333 2500 2500
44444 4500 4500
44444 4500 3000
44444 4500 200
44444 4500 2500
44444 4500 200

If this post helps click Yes