Results 1 to 2 of 2

Thread: Dragging a repeatable and increasing series

  1. #1

    Question Dragging a repeatable and increasing series



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Does anyone have any suggestions to do the following. I am trying to generate a unique reference number which repeats x number of times (in this example 3 times) before increasing.

    BO-20-100-10001
    BO-20-100-10001
    BO-20-100-10001
    BO-20-100-10002
    BO-20-100-10002
    BO-20-100-10002
    BO-20-100-10003
    BO-20-100-10003
    BO-20-100-10003
    and so forth.

    I know how to create this list when each unique value is listed once. The trouble I am having is repeating the unique value x number of times and then increasing. Am I overlooking a very simple method for executing this type of series?

    Regards
    -Sam

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,251
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Assuming that A2 holds BO-20-100-10001

    Put this in A3 and copy down: =IF(COUNTIF(A$2:A2,A2)=3,LEFT(A2,10)&VALUE(RIGHT(A2,5))+1,A2)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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