Sequential ID for groups of consecutive values

[SAMARS]

Hello,
I have data that is in sequential order. One column contains data that is either 1 or 0. I want to create a new column that numbers the groups of consecutive 1's.[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

What is the best way to generate column B if given data like in column A?

Any help is appreciated,
SAMARS

 

[jtakw]

Hi,

Not absolutely sure if this is what you want, you'll need to add a "dummy" row on top of your data which you can hide:

Book1
	A	B
1
2	1	1
3	1	1
4	0
5	0
6	1	2
7	1	2
8	1	2
9	0
10	1	3
11	1	3
12	0
13	0
14	0
15	1	4
16	1	4
Sheet1

Cell Formulas
Range	Formula
B2	=IF(A2=A1,B1,IF(A2=1,MAX(B$1:B1)+1,""))

B2 formula copied down.

 

[inactiveuserps07]

@SAMARS, I know some people here frown on the use of INDIRECT and OFFSET; but if your setup doesn't allow you to have the dummy row up top of your data as shown above, this longer formula in B1 and copy-dragged down will not require use of the dummy row:

=IF(OR(ROW()=1,AND(IFERROR(OFFSET(A1,-1,0)<>1,FALSE),IFERROR(OFFSET(A1,-1,0)<>0,FALSE))),1,IF(A1=0,"",IF(OFFSET(A1,-1,0)=0,MAX(INDIRECT("B$1:"&ADDRESS(ROW()-1,COLUMN())))+1,OFFSET(B1,-1,0))))

There may be a shorter way. In any case, it's another option for you.

 

[Rick Rothstein]

With your original data starting in cell A1, put a 1 in cell B1, then put this formula in cell B2 and copy it down to the end of your data...

=IF(A2=0,"",IF(A1=0,1+MAX(B$1:B1),B1))

 

[SAMARS]

Thank You! This worked great!

 

[jtakw]

You're welcome, welcome to the forum.