How to consolidate non consecutive numbers in a column?

[excelos]

Hello

I want to consolidate (sum) the consecutive only numbers in a column (i.e. if numbers are non consecutive, then they should not be added).

For example, in the below table:

Column A	Column B
K	3​
K	2​
K
K	3​
K
K	4​
L	2​
L	1​
L	2​
L
L	3​

I need to consolidate/sum:
For K in Column A, the cells B2, B3 together and no other cell (as they are not consecutive).
For L in Column A, the cells B8, B9, B10 no and other cell (as they are not consecutive).

The table then would look like below, if we put the consolidated numbers (sums) into Column C

Column A	Column B	Column C
K	3​	5​
K	2​	5​
K
K	3​
K
K	4​
L	2​	5​
L	1​	5​
L	2​	5​
L
L	3​

Any neat way for this? Even with pivot!
Thanks!

 

[awoohaw]

what if there are multiple sets of consecutive numbers for a (or some) groups?

 

[Phuoc]

Try this:

Book1
	A	B	C
1
2	K	3	5
3	K	2	5
4	K
5	K	3
6	K
7	K	4
8	L	2	5
9	L	1	5
10	L	2	5
11	L
12	L	3
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF(B2="","",IF(AND(A2=A1,B1<>""),C1,IF(AND(A2=A3,B3<>""),SUM(B2:INDEX(B2:B1000,MATCH(0,(B2:B1000<>"")*(A2:A1000=A2),0))),"")))

 

[kvsrinivasamurthy]

IN C2

=LET(a,A2:A12,b,B2:B12,c,IF(b<>"",IF((ABS(B3:B13-B2:B12)=1)+(ABS(B1:B11-B2:B12)=1),1,0),""),MAP(a,c,LAMBDA(x,y,IF(y=1,SUM(b*(x=a)*(c=1)),""))))

 

[kvsrinivasamurthy]

Macro with VBA code is ok .