SOLVED - Need help with a non-sequential number pattern

1. 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.

2. Re: Need help with a non-sequential number pattern

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

Pete

3. 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

4. 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!

5. 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. 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. 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. 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. 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. 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. 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. 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.

13. 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.....

14. 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. 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

16. 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. 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

18. 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

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

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