Sequential ID for groups of consecutive values

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
    2,208
    Post Thanks / Like
    Mentioned
    5 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 09:06 PM.

  3. #3
    Board Regular
    Join Date
    May 2017
    Location
    Boston
    Posts
    732
    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
    30,841
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 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
    2,208
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sequential ID for groups of consecutive values

      
    You're welcome, welcome to the forum.

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
  •  

 

 
DMCA.com