Sequential ID for groups of consecutive values

SAMARS

New Member
Joined
Dec 6, 2017
Messages
2
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.
11
11
0
0
12
12
12
0
13
13
0
0
0
14
14

<tbody>
</tbody>

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

Any help is appreciated,
SAMARS
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
AB
1
211
311
40
50
612
712
812
90
1013
1113
120
130
140
1514
1614
Sheet1
Cell Formulas
RangeFormula
B2=IF(A2=A1,B1,IF(A2=1,MAX(B$1:B1)+1,""))


B2 formula copied down.
 
Last edited:
Upvote 0
@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.
 
Upvote 0
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))
 
Upvote 0
You're welcome, welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top