Sum similar groups as total for each group

[YasserKhalil]

Hello everyone
I have two columns A and B like that
5 Up
3 Up
2 Up
4 Down
1 Up
3 Up
6 Down
8 Down

And I have the following code that sums the total for each group and put the total at the end of each group. I could do it with loops by this code

Code: Select all

Sub Test()
    Dim r As Long, x As Double
    For r = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If Cells(r, 2).Value = Cells(r + 1, 2).Value Then
            x = x + Cells(r, 1).Value
        Else
            Cells(r, 3).Value = x + Cells(r, 1).Value
            x = 0
        End If
    Next r
End Sub

Is it possible to do such a task using formulas? or a code using arrays as the sheet has about 30K of rows?

[HansV]

Here is a version using arrays:

Code: Select all

Sub Test2()
    Dim v
    Dim w
    Dim r As Long
    Dim x As Double
    Dim m As Long
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:B" & m + 1).Value
    ReDim w(1 To m, 1 To 1)
    For r = 1 To m
        x = x + v(r, 1)
        If v(r, 2) <> v(r + 1, 2) Then
            w(r, 1) = x
            x = 0
        End If
    Next r
    Range("C1:C" & m).Value = w
End Sub

[YasserKhalil]

Thank you very much my tutor. Is it possible to do that using formulas or it will be complicated?

[HansV]

I'd use a helper column to emulate the variable x.
In D1:

=A1

In D2:

=A2+IF(B2=B1,D1,0)

Fill down from D2 to the end of the data.
In C1:

=IF(B1=B2,"",D1)

Fill down from C2 to the end of the data.
You can hide column D if desired.

S0136.png

[Doc.AElstein]

Hello
This solution is a bit of a fiddle. It might be more trouble then its worth…_

_.. Originally I was thinking of an Evaluate Range type solution, evaluating a spreadsheet formula. But I couldn’t figure out a working formula, so I gave up completely ..

But then later I saw Hans formulas answer, so I tried to use those.
But there is a small snag: His second formula, …._
In D2: =A2+IF(B2=B1,D1,0) , copied down
_... will work in the spreadsheet if its copied down, but/ because it relies on the formula having been done already in the previous row.
This gives problems with the Evaluate Range technique, because it will then only work “one row down”.
One workaround to this is to repeat the Evaluate range second formula code line for as many times as you may have repeated consecutive values in column B.
If you have an estimate for the likely number of repeated consecutive value in column B ,
and
if it small number
and
if the solution works at all for 30K of rows,
then
the solution is possibly worth a quick try

This is an example assuming that you would not have more than 3 consecutive Ups or Downs ( NoCnsUpDn = 3 )

Code: Select all

Sub RangeEvaluateHansFormulasEm3() '  http://www.eileenslounge.com/viewtopic.php?p=281028#p281028
Dim Em As Long: Let Em = Range("A" & Rows.Count).End(xlUp).Row
'  ....... In D1:     =A1
 Let Range("=D1").Value = Evaluate("=A1")
'  ....... In D2:     =A2+IF(B2=B1,D1,0)
' Hans second formula needs that the last row is done already, so the Evaluate range will only get it right the first time, so it would need to be repeated for as many times as you might have consequtine same values in column b
Dim Cnt As Long, NoCnsUpDn As Long: Let NoCnsUpDn = 3
    For Cnt = 1 To NoCnsUpDn Step 1
     Let Range("=D2:D" & Em & "").Value = Evaluate("=A2:A" & Em & "+IF(B2:B" & Em & "=B1:B" & Em - 1 & ",D1:D" & Em - 1 & ",0)")
    Next Cnt
'  .....In C1:      =IF(B1=B2,"",D1)       Fill down from C2 to the end of the data.
 Let Range("=C1:C" & Em & "").Value = Evaluate("=IF(B1:B" & Em & "=B2:B" & Em + 1 & ","""",D1:D" & Em & ")")
End Sub

Alan

[YasserKhalil]

Thanks a lot Mr. Hans for the solution of formulas.
Thanks a lot Mr. Alan. I didn't get this statement (assuming that you would not have more than 3 consecutive Ups or Downs ( NoCnsUpDn = 3 )) .. The number of Ups or Downs are dynamic (maybe 1 and may be 10 or more. There is no fixed rule for that as the data is dynamic)

[Doc.AElstein]

Hi Yasser,

... I didn't get this statement (assuming that you would not have more than 3 consecutive Ups or Downs ( NoCnsUpDn = 3 )) ..
The number of Ups or Downs are dynamic (maybe 1 and may be 10 or more. There is no fixed rule for that as the data is dynamic)

If you don't know what the maximum number of consecutive Ups or Downs could be and it could be maybe 100 or 1000 or more, then my solution is most likely no use to you.

That’s the point:

....
If you have an estimate for the likely number of repeated consecutive value in column B ,
and
if it small number
and
if the solution works at all for 30K of rows,
then
the solution is possibly worth a quick try

_._____________________________

If, for example , you think you will never have more than 20, then NoCnsUpDn = 20

If you can never know, and all could be Ups or all could be Downs then NoCnsUpDn = Em. In this case the array macro from Hans would probably be comparable in performance, maybe a bit better , or bit worse, I don’t know for sure.

Alan

[YasserKhalil]

Thank you very much for your contribution Mr. Alan
I appreciate a lot your help.