Sum all repeated values in consecutive order

[15dgs]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Fruit
[/TD]
[TD]#
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]6
[/TD]
[TD]NO VALUE
[/TD]
[/TR]
[TR]
[TD]Orange
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Apple
[/TD]
[TD]9
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Banana
[/TD]
[TD]1
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]Banana<strike></strike>
[/TD]
[TD]10
[/TD]
[TD]NO VALUE<strike></strike>
[/TD]
[/TR]
[TR]
[TD]Banana<strike></strike>
[/TD]
[TD]4
[/TD]
[TD]NO VALUE<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]


How do I consolidate a column but only if they're in consecutive order? I can't just use the consolidate feature because that will total all of the apples but I only want to total them if they come right in a row. Thanks!

 

[PaddyD]

Hi - welcome to the board.

Can you be a bit more specific - given the data you've provided, what results are you trying to produce..?

 

[15dgs]

Hi - welcome to the board.

Can you be a bit more specific - given the data you've provided, what results are you trying to produce..?

Sure! So basically I have a string of a few thousand lines of data that's similar to the table above that I need to analyze for a pattern. However, the device that recorded my data gave results back ever few milliseconds even if it was the same result for numerous seconds in a row. So I now need an equation that will sum the amount of time on each stimulus that happened back to back but I can't have it compromise the order it occurred in. Does that make more sense? Thanks!

 

[Tetra201]

See if this VBA sub works for you:

Sub Sum_Rpt_Val()
    Dim cll As Range, out As Range, ttl As Double
    Set out = Range("C2")
    ttl = Range("B2").Value
    For Each cll In Range([A2], Cells(Rows.Count, "A").End(xlUp).Offset(1))
        If cll.Value = cll.Offset(-1).Value Then
            ttl = ttl + cll.Offset(, 1).Value
        Else
            out.Value = ttl
            ttl = cll.Offset(, 1).Value
            Set out = cll.Offset(, 2)
        End If
    Next cll
End Sub

 

[15dgs]

I love you. You are a genius. I have very limited knowledge of VBA's but this worked. Thank you!

 

[Tetra201]

Glad to have helped.

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=IF($A2=$A1,"NO VALUE",SUM(OFFSET($B2,0,0,MAX(FREQUENCY(IF(A2:$A$8=$A2,ROW(A2:$A$8)),IF(1-(A2:$A$8=$A2),ROW(A2:$A$8)))))))

 

[Nishant94]

Unknown
	A	B	C
1	Fruit	#	Total
2	Apple	2	8
3	Apple	6	NO VALUE
4	Orange	7	7
5	Apple	9	9
6	Banana	1	15
7	Banana	10	NO VALUE
8	Banana	4	NO VALUE
Sheet6

Cell Formulas
Range	Formula
C2	{=IF(A2=A1,"NO VALUE",SUM(B2:INDEX(B2:B$8,MIN(IF(A2:A$8<>A3:A$9,ROW(A2:A$8)-ROW(A2)+1)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.