Auto Fill Issue Numbers 1 - 70000 repeated each 30 times

0

I am trying to get the spreadsheet to auto fill a sequence of numbers starting with 1 and ending with 70000. Each number should repeat 30 times as counted out in column c and then change to the next number in sequence for a total of 2.1milion lines.

I have tried to highlight and drag but it does not autofill the correct sequence is there anyway to do this?

Thanks

Answer
Discuss

Answers

0

Your version of Excel has only around 65,000 rows so it won't work in a single column. The new versions of Excel, from 2007 and up only have a little over 1 million rows, so you will need at least two columns in there.

However, you can increment a number like you want.

First, input 1 in the first cell.

Second, input this formula in the next cell below the first one:

=IF(MOD(ROW()-1,30)=0,C1+1,C1)

I assumed you put the 1 into cell C1 and the formula into cell C2.

Copy the formula down as far as you would like or are able to.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login