Calucate total amount of cost centre apply total at the end of each type of cc

[Himanshu]

Hi Everyone,


I am wondering if anyone can help in the following excel riddle.

I do many extractions from the system at the end of the month. And Extractions look like below format. Cost centre starting from S and U representing two different entities. And frequency of these Cost centre is not fixed sometimes its 40 or 60 in numbers. I request you provide some formula or macro that every time i do these extractions i simply run the macro or apply the formula to get the desired result as shown below on the right side. Formula or macro must calculate total amount under one cost centre type and provide it right below where a particular cost centre line item finishes. And again check for the next type of cost centre and provide total right below after that cost centre line time finishes.


Cost centre Amount Cost centre Amount

S1234 3444.44 S1234 3444.44

S2345 2343.67 S2345 2343.67

S2332 9344.44 S2332 9344.44

S2223 9877.66 S2223 9877.66

U1234 3444.44 Total xxxxxx

U3452 2343.67 U1234 3444.44

U2332 9344.44 U3452 2343.67

U2223 9877.66 U2332 9344.44

U2223 9877.66

Total xxxxxx

Many Thanks in Advance

 

[Hui]

Why not just split Column 1 into 2 using Column to Text and split of the first character or add another column with +left(Column1,1)

Then Use the Data, Subtotal Command on the whole table and Sum the amount when the New Column Changes

eg:

[pre]

Cost	Amount
S	S1234	3444.44
S	S2345	2343.67
S	S2332	9344.44
S	S2223	9877.66
S Total		25010.21
U	U1234	3444.44
U	U3452	2343.67
U	U2332	9344.44
U	U2223	9877.66
U	U2223	9877.66
U Total		34887.87
Grand Total	59898.08

[/pre]

 

[Himanshu]

Thanks Hui,,It works wonder.