# Thread: Sequential ID for groups of consecutive values

1. ## Sequential ID for groups of consecutive values

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.
 1 1 1 1 0 0 1 2 1 2 1 2 0 1 3 1 3 0 0 0 1 4 1 4

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

Any help is appreciated,
SAMARS

2. ## Re: Sequential ID for groups of consecutive values

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:

AB
1
211
311
40
50
612
712
812
90
1013
1113
120
130
140
1514
1614

Sheet1

Worksheet Formulas
CellFormula
B2=IF(A2=A1,B1,IF(A2=1,MAX(B\$1:B1)+1,""))

B2 formula copied down.

3. ## Re: Sequential ID for groups of consecutive values

@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:

Code:
`=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.

4. ## Re: Sequential ID for groups of consecutive values

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))

5. ## Re: Sequential ID for groups of consecutive values

Thank You! This worked great!

6. ## Re: Sequential ID for groups of consecutive values

You're welcome, welcome to the forum.