Sequential numbering by 6

[BattyKat]

I am creating a file to print on some cards. The same number has to repeat 6
times and then be in sequential order. Because I have '0001 , I can not do
any of the quick fill options because of the '.
For ex.
'0001
'0001
'0001
'0001
'0001
'0001
'0002
'0002
ETC....

I need to number to 357-- is there an easy way to do this ? I need to have
4 positions on my printed sheet because its a parking pass and the vendor
requires the numbers to match their lot numbers.

 

[Dave Peterson]

You could put this in a cell in row 1 and drag down as far as you needed:

=TEXT(1+(INT((ROW()-1)/6)),"0000")

 

[Tom Hutchins]

Here is one way....

Instead of '0001 in A1:A6, just enter the number 1. Make column A
left-justified. In A7 enter this formula:

=IF(COUNTIF(A1:A6,A6)<6,A6,A6+1)

Copy A7 down through row 2142. Select column A, then select Format >> Cells.
On the Number tab, select Custom. In the Type box, enter 0000, then click OK.

Hope this helps,

Hutch

 

[T. Valko]

Try this...

On an empty sheet, hit function key F5. In the reference box type: A1:A357
then hit ENTER.

This will select the range A1:A357. With this range still selected type this
formula:

=ROW()

Hold down the CTRL key then hit ENTER.

Enter this formula in C1:

=INDEX(A:A,CEILING(ROWS(C$1:C1)/6,1))

Copy down to C2142 (357*6 = 2142)

With the range C1:C2142 still selected
Goto Edit>Copy
Then, Edit>Paste Special>Values>OK
Format the range to display the leading 0s:
Goto Format>Cells>Custom
Type: 0000
OK

Delete column A

This should take you all of 2 minutes to do!

 

[BattyKat]

THANKS SO MUCH-- YOUR FORMULA WORKED LIKE A CHARM IN OUR DATABASE!