Running total in Excel to a value, reset the cumulative total, and group?

[nick_ops]

I am looking to group a list of numbers based on their sums to a target value. The database is large and would need to be a scalable solution. For instance, if my target value was 10, this would look like the following:

Item	Value	Total	Group
A	4		I
B	3		I
C	4	11	I
D	3		II
E	2		II
F	7	12	II
G	10	10	III
H	12	12	IV

What are the formulas I can do this in the Total and Group columns?

 

[sxhall]

Hi nick_ops

I used a bit of VBA to do this, below. It does not use numerals but adds in a number count for each group.

Sub SumNGroup()

Dim RunTot As Integer
Dim TotTar As Integer
Dim ValCount As Integer
Dim OffRow As Integer
Dim RowCount As Integer

With Application
    .ScreenUpdating = False
End With

TotTar = Range("E1").Value ' This cell has the value at which to cut off for the total

For Each i In Range("B2:B9") ' Amend range here or use the line below for selected cells
'For Each i In Selection

If OffRow = 0 Then
OffRow = i.Row
ValCount = ValCount + 1
End If
RunTot = RunTot + i.Value
RowCount = RowCount + 1

If RunTot >= TotTar Then
    i.Offset(0, 1).Value = RunTot
    Range("D" & OffRow).Resize(RowCount, 1).Value = ValCount
    RunTot = 0
    OffRow = 0
    RowCount = 0
End If

Next i

With Application
    .ScreenUpdating = True
End With

End Sub

Below are the results I got when running the above code.

Steven