Results 1 to 6 of 6

Thread: Sequential ID for groups of consecutive values

  1. #1
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    4,075
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by jtakw; Dec 6th, 2017 at 08:06 PM.

  3. #3
    Banned user
    Join Date
    May 2017
    Posts
    748
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,229
    Post Thanks / Like
    Mentioned
    59 Post(s)
    Tagged
    22 Thread(s)

    Default 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))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Dec 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Sequential ID for groups of consecutive values

    Thank You! This worked great!

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    4,075
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sequential ID for groups of consecutive values

    You're welcome, welcome to the forum.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •