How to add a number sequence to spreadsheet

[Mile029]

I'm trying to add a number sequence to my spreadsheet but I have over 20,000 lines to add this to. Is there an easy way to do this. See my example below:

'00000001
'00000001
'00000002
'00000002
'00000003
'00000003

I need it to continue to line 20,000. This is the break that our accounting software recognizes as the start of a new entry.
Every two lines have the same number, then the next two, it can be random numbers but has to be different from any two lines in the sheet.
Please advise. Thanks.

[Ron Coderre]

It's really important to let us know which version of Excel you are using so we can give accurate instructions.

With Excel 2010
Try this
A1: '00000001
• Select A1:A20000
• Home.Fill.Series...Check:AutoFill
• Click: OK

That should do it....Does it?

[Mile029]

Sorry Ron, I thought I had set that up when I registered, I'm using Excel 2003.

[Ron Coderre]

Sorry Ron, I thought I had set that up when I registered, I'm using Excel 2003.

Ok...For Excel 2003:
A1: '00000001
• Select A1:A20000
• Edit.Fill.Series....ChecK: AutoFill...Click: OK

Does that help?

[Mile029]

Also, I need the numbers to be repeating every two lines, so it would be as follows:

A1: '00000001
A2: '00000001
A3: '00000002
A4: '00000002
A5: '00000003
A6: '00000003

and so on 4 , 5 , 6 ....

Autofill only numbers sequentially like as follow:

A1: '00000001
A2: '00000002
A3: '00000003
A4: '00000004

I need it repeat every two lines, if you need to upload a spreadsheet to explain I can. thanks.

[Mile029]

If I do the above for for Excel 2003 autofill, it just repeats the series. meaning the following:


A1: '00000001
A2: '00000001
A3: '00000002
A4: '00000002
A5: '00000003
A6: '00000003
A8: '00000001
A9: '00000001
A10: '00000002
A11: '00000002
A12: '00000003
A13: '00000003

does not create a new '00000004 in A8 and A9 and then '00000005 in A10 and A11 and so on and so on....

[Ron Coderre]

Sorry...My mistake. I didn't notice the repeats

Try this
A1: '00000001
A2: '00000001

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Then, if needed
• Copy the range of formulas and Edit.Paste_Special.Values over themselves.
• Change the number format of those cells to TEXT (So they won't convert to numbers if you edit them)


does that help?

[Mile029]

Yes, that works awesome, I knew it had to be simple, I was over-thinking it. Thanks Ron!

[bebo021999]

Try this:
* Type 1 in A1
* In A2:

Please Login or Register  to view this content.

Copy till line 2000
* Highline A1:A2000,Format Cells.Number.Custom.Type: 000000
Hope this help