+ Reply to Thread
Results 1 to 18 of 18

SOLVED - Need help with a non-sequential number pattern

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    SOLVED - Need help with a non-sequential number pattern

    Hello all! I am a relative newbie to Excel. I am using Excel 2007. I've been tasked with creating some numbered event vouchers. However, I am doing these 8 up to a page. After I print I am cutting them into stacks. It would be great if the stacks were numbered sequentially. But, that means I need a number pattern which goes in 8s and then comes back around to catch the remaining numbers.

    Example: 1, 9, 17, 25, 33, 41, 49, 57, 2, 10, 18, 26, 34, 42, 50, 58, 3, 11, 19, etc.

    If this is at all possible I would love to know. It isn't totally necessary to have my stacks be sequential. I just thought it would look pretty fancy if they were. And I'd love to do this on the printing end and not have to hand sort them afterwards.

    Any help would be appreciated.
    Last edited by desertdweller; 10-15-2012 at 12:02 PM. Reason: Problem solved

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Need help with a non-sequential number pattern

    So where do you want these numbers to appear? (Which cells?)

    Pete

  3. #3
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: Need help with a non-sequential number pattern

    desertdweller,

    As Pete says, you have not given much info regarding format, however the attached produces the patrtern you are after.

    Dave H
    Attached Files Attached Files
    - Mark your post [SOLVED] if it has been answered satisfactorily, by editing your original post using advanced mode.
    - Thank those that provided useful help, its nice and its very well appreciated...use the star on the lower left of the post

  4. #4
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Ah, well this is my last thing to do, so I just need one column to do this. Starting A-1 and going down. I can't do this in multiple columns. Which is why I am stumped on whether it is possible.

    Thanks so much!
    Last edited by desertdweller; 10-12-2012 at 12:54 PM.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Need help with a non-sequential number pattern

    Put the number 1 in A1, and this formula in A2:

    =IF(MOD(ROWS($1:2)+7,8)=0,INT((ROWS($1:2)-1)/8+1),A1+8)

    This will give you the pattern you require up to 64, but beyond there it will repeat. Do you need to have numbers beyond 64, and if so will they follow the above pattern but with 64 added on for the next block of 64 (and then 128 added on for the block after that etc.)?

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Pete,

    This is VERY helpful. Thank you. Yes, I will need more than 64. Ha. This is going to be a heavily attended event, and people can be given multiple vouchers. So, I will probably need something on the order of 15,000. Which I is why I didn't want to have to enter all the numbers manually. Especially as I am just volunteering. (When I took on the assignment, no one was asking for numbering. Joke's on me.)

    So, yes, I need this to go to a very high number.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Need help with a non-sequential number pattern

    Okay, I'll get back to you later - it's half-time in the football match at the moment.

    Pete

  8. #8
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Pete,

    I cannot express my thanks to you enough. You are my hero for the day! Thank you, thank you, thank you!

    Mindy

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Need help with a non-sequential number pattern

    Hi Mindy,

    put this revised formula in A2:

    =IF(MOD(ROW()+7,8)=0,INT((ROW()-1)/8)+1+INT((ROW()-1)/64)*64+INT((ROW()-1)/4096)*4096,A1+8)

    then copy it down. This will repeat again only after 4096*4096, or 16.78 million, and as there aren't that many rows in an Excel sheet then you should be okay.

    Hope this helps.

    Pete

  10. #10
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Pete,

    Thanks for your work on this. I noticed when I put this formula in that it often skips a bunch of numbers. After the sequence "8, 16, 24, 32, 40, 48, 56, 64" it doesn't go back to 9. It skips up to 73. There are a number of large jumps like this. Instead of going from 80 back to 81 it jumps up to 145, etc. When I tried going to 15000, the final number showing was 29140. It definitely kept the pattern all the way to the bottom, however it just often skipped a whole bunch of numbers.

    Mindy

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: Need help with a non-sequential number pattern

    Hi Mindy,

    I've tried all sorts of formulae, but I think the simplest solution will be to use the first formula I gave you in A2 and copy that down to cell A64. Then in cell A65 put this formula:

    =A1+64

    and copy that down to cell A4096. Then in cell A4097 put this formula:

    =A1+4096

    and copy that down as far as you need to.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Thanks. But, this is still jumping from 64 to 73. And it goes from 128 to 137. It doesn't go back after 8-64 to pick up 9, 10, etc. I get that it is trying not to repeat numbers. It is tricky to do this without repeating numbers.

    Ugh. Maybe I am going to have to sit here today and input the missing sequences manually.

    Still, this helped a lot. It accomplishes over half the work.

    Thanks.
    Last edited by desertdweller; 10-14-2012 at 01:43 PM.

  13. #13
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    Wait, never mind, I think I have figured out the problem. Wasn't thinking very logically there. Yeesh, that was stupid. Hang on and I will see if I can fix it....

    What I actually need is for these to go in groups of 100. So, it should go 1, 101, 201, 301, 401, 501, 601, 701, 2, 102, 202, 302, 402, 502, 602, 702

    And then go back to 801, 901, 1001, 1101, 1201.....
    Last edited by desertdweller; 10-14-2012 at 02:10 PM.

  14. #14
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    If anyone can help me with this ASAP I would greatly appreciate it. I am working on a deadline. Ugh, I wish I hadn't been so stupid to start with.

    I tried manually filling in about half the sequence to see if Excel would recognize it and fill the rest in, but no luck.

    Pete, thanks so much for all that work. Your formula did exactly what I asked for. I was just being a moron and didn't know what I was asking for.

    Mindy

  15. #15
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: Need help with a non-sequential number pattern

    OK single column, in your new sequence to more than 15000..

    Does that do it?

    Dave H
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Need help with a non-sequential number pattern

    That list is definitely much closer. Thanks. But, the round numbers are not right. Look at the 800 row. That should be the number 800. The round numbers there should be 100, 200, 300, 400, 500, 600, 700, 800. The row of 1600 should be number 1600. Etc. The whole sequence gets weird around those round numbers.

    And it weirdly makes a leap at number 4795. Instead of going back to 4096, it goes up to 4796. It makes another weird leap in the "95" section of the next sequence, too. Haven't looked at anything past those. Actually, after a quick glance, it gets really weird at the "95" section of every sequence. For some reason, it seems to jump to the next 800 numbers for numbers ending in "95-00". How weird.

    But, this is workable. I will have to manually fill in some of the missing numbers. And re-sequence a few. Still, it shouldn't take too long.

    If anyone can give me one that is fully accurate with which to compare, that would be great for proofreading.

    Thanks!

    PS. Actually, my Excel program is filling in the series for me in those "90" endings where that chart gets weird. So, I am not having to do much extra formatting.

    Thanks so very much! Thank you, thank you, thank you!!!!

  17. #17
    Forum Contributor Dave H9's Avatar
    Join Date
    03-10-2011
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    418

    Re: Need help with a non-sequential number pattern

    I think I copied it down wrong, see if it's correct now. I have checked and when reordered there are no duplicates and seem to increment correctly.

    Dave H
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    10-11-2012
    Location
    Phoenix
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: SOLVED - Need help with a non-sequential number pattern

    Perfect!

    Pete and Dave, I cannot thank you enough for this work. It has helped me out tremendously. Thank you so very much!

    Mindy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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