+ Reply to Thread
Results 1 to 7 of 7

Fill down a row count until a new value is reached?

  1. #1
    Registered User
    Join Date
    03-28-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Question Fill down a row count until a new value is reached?

    Newbie question - thanks for any help.

    I have the following:

    31
    2
    4
    4
    2
    3
    61
    9
    3
    2
    31
    2
    3
    4

    And would like a formula to number the rows until the next 1 occurs. So in the end, I want it to look like:

    31
    22
    43
    44
    25
    36
    61
    92
    33
    24
    31
    22
    33
    24

    It's about 500,000 rows long, so any formula shortcut would be appreciated.

    Thanks!


  2. #2
    Registered User
    Join Date
    03-28-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Fill down a row count until a new value is reached?

    sorry if it's unclear that the 1's are in a separate column...

    3 1
    2
    4
    4
    2
    3
    6 1
    9
    3
    2
    3 1
    2
    3
    2

  3. #3
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Fill down a row count until a new value is reached?

    attached please find the file with both working option a macro and the formulas.

    the macro requires less overhead since it does not need to be copied down
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-28-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Fill down a row count until a new value is reached?

    Thanks - but the numbers are in separate columns. Please see attached for an example.
    Attached Files Attached Files

  5. #5
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Fill down a row count until a new value is reached?

    Perhaps

    In H1

    =IF(B1=1,1,COUNT(B1:B1))

    In H2 and copy down.

    =IF(B2=1,1,COUNT(A2:A2)+H1)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    03-28-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Fill down a row count until a new value is reached?

    Fotis - Thank you, that worked perfectly.

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Fill down a row count until a new value is reached?

    .........................

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1