+ Reply to Thread
Results 1 to 10 of 10

Sequential numbering formula omitting 0's and resetting after X rows

  1. #1
    Registered User
    Join Date
    11-11-2014
    Location
    Iowa
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Sequential numbering formula omitting 0's and resetting after X rows

    I have a series of data that is hundreds of rows. I have attached a sample file. In column E I want an "ID" that starts at 1 and sequentially numbers skipping any 0 values that are in column D. I was able to accomplish that with the below formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The part that I haven't been able to figure out is that I want the numbering to "reset" after a specific number of rows. The number of rows will be specified by a cell. In my example file, cell J1 indicates that the count should reset after 15 rows. I have also included what the desired result should look like in column F.

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,160

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Why don't you just use the change in inventory number to do this? Not sure quite what the significance of 15 is.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    11-11-2014
    Location
    Iowa
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Thanks for the response! I would be ok using either the number OR the change in inventory number, but I am uncertain how to accomplish it that way either.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,160

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Great - glad I spotted it, because it makes it much easier!

    =IF(D2=0,"",COUNTIFS($D$2:$D2,">0",$A$2:$A2,A2))

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    I came up with the same as Ali, before seeing her response, i.e. in E2:

    =IF(D2=0,"",COUNTIFS($D$2:$D2,">0",A$2:A2,A2))

    Copy down as required.

    Pete

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,160

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Snap, Pete!

  7. #7
    Registered User
    Join Date
    11-11-2014
    Location
    Iowa
    MS-Off Ver
    MS Office 2013
    Posts
    8

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Thank you very much. That worked perfectly!!

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,160

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    You're very welcome!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    Agh!! I got 3 email notifications of Post #7 - hope the troubles are not starting up again.

    Pete

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,160

    Re: Sequential numbering formula omitting 0's and resetting after X rows

    I suspect they might be, Pete ...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sequential Numbering
    By Filtec in forum Excel General
    Replies: 7
    Last Post: 04-15-2013, 04:01 AM
  2. Resetting a formula every 43 rows
    By Insert Name in forum Excel General
    Replies: 2
    Last Post: 01-06-2013, 07:24 AM
  3. Sequential numbering
    By mojura in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-17-2009, 01:52 PM
  4. Sequential numbering and Rows
    By mkarylyle in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-18-2008, 05:54 PM
  5. Convert a Value to Sequential Numbering of Rows
    By rx111 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2007, 02:14 PM
  6. Sequential Numbering
    By Tom in forum Excel General
    Replies: 4
    Last Post: 08-21-2006, 05:25 PM
  7. [SOLVED] Sequential Numbering.
    By Rodney in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-02-2005, 05:06 PM

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