Sum, when data in adjoining column changes

[Z Man]

I have spreadsheet with thousand of lines. Column A lists a name, and Column
lists a percentage. When the name changes, I need to sum the percentages
(they are supposed to add up to 100%) in Column C. Example:

Column A Column B Column C

AAA .50
AAA .25
AAA .20
AAA .05 1.00
BBB .10
BBB .10
BBB .75
BBB .04
BBB .01 1.00

(Sometimes the data in Column could be a number, such as 99-9999999.)

Doing this by hand will take many hours, and I have to periodically repeat
the process. Is there a formula I can use?

 

[Ken Wright]

Do you really need it in Col C. If you don't mind having it at the bottom of
each section of data, you can use Data / Subtotals to do this for all your data
in about 30 seconds.

Select all your data, do Data / Subtotals / For each change in A, SUM B

 

[Chip Pearson]

As Ken wrote, the automatic SubTotals tool is ideal for this sort of thing.
However if you need the values in column C rather than B, try something like
the following. Change the reference to A1 to the first cell in your data
range.

Dim Rng As Range
Dim Total As Double
For Each Rng In Range(Range("A1"), Range("A1").End(xlDown)(2, 1))
Total = Total + Rng(1, 2).Value
If Rng.Value <> Rng(2, 1).Value Then
Rng(1, 3).Value = Total
Total = 0
End If
Next Rng

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)