Summing lists of consecutive numbers

[RumbleJ]

Hello, I'm trying to figure out a way to better process the data that another program that I use spits out. It gives me a list of numbers with breaks in them and I need to be able to add up all of the numbers just in the contiguous blocks of numbers like this:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]AAA[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]BBB[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]4[/TD]
[/TR]
</tbody>[/TABLE]

So for this example I would need to get the answer of 4 for the first group and 7 for the second and something that would work with any arbitrary sized list. Also, sometimes it'll break apart a list (though not at a consistent length) so you'll get another list like this below the last:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]BBB[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]5[/TD]
[/TR]
</tbody>[/TABLE]

So in this case then I'd be a huge help if it could see that an identical list was started previously (or that one follows) and instead return a result of 15. They will be consecutive so it's not like you'll see AAA;BBB;CCC;BBB or anything like that but there are times where it posts empty sets as well i.e.:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]AAA[/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]AAA[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully that explains it clear enough. Thank you in advance for the help.

 

[lrobbo314]

The code below assumes that your data is in columns A and B. The results are output to columns G and H.

Sub GroupTotals()
Dim AR() As Variant: AR = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
Dim r As Range: Set r = Range("G1")
Dim SD As Object: Set SD = CreateObject("Scripting.Dictionary")
Dim Pos As String

For i = LBound(AR) To UBound(AR)
    If Not IsEmpty(AR(i, 1)) And IsEmpty(AR(i, 2)) Then
        Pos = AR(i, 1)
        SD.Add Pos, 0
    ElseIf Pos <> "" Then
        SD(Pos) = SD(Pos) + AR(i, 2)
    End If
Next i

Set r = r.Resize(SD.Count, 1)

With r
    .Value = Application.Transpose(SD.keys)
    .Offset(, 1).Value = Application.Transpose(SD.items)
End With

End Sub

 

[RumbleJ]

Thank you for your quick reply. I've been trying to figure this out using weird combinations of formulas and whatnot since I know very little about VBA. I tried to copy your code into a new module and then I edited the

Dim AR() As Variant: AR = Range("A1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value
Dim r As Range: Set r = Range("G1")

values to match what is in the spreadsheet but I'm not sure what to do next. Nothing happened automatically and when I try going to View Macros > Run I get Run-time error '457' This key is already associated with an element of this collection. If I knew more about VBA I might be able to tweak your code to match my use case but I'm sad to say that I am not. You'll probably need more exact parameters in this case so here's the actual format of the numbers:

[TABLE="class: grid, width: 170"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]junk txt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]junk txt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]List Name[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]junk txt[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Again, I was hoping I would be able to figure at least part of this out myself but VBA really is beyond me. As you can see the List Name is actually to the right and 5 cells up from the start of the list and surrounded by some junk text (these are also columns E & F in the raw output file). This will always be the case except for when there's an empty set and the junk text and List Name repeat without any numbers in between, the right column only contains the list of numbers. Also, when I did attempt your code I inserted a copy of the right column to the left of the list since I figured that mattered. It's possible to Find & Replace the top 2 cells of junk text with blank cells but the bottom one varies with the List Name. Thank you again for the help.