Trying to create a repeating list of numbers

[Jinjunwei]

Hello Everyone.

Im trying to create a list of repeating numbers excel. For example, I'd like to have a column (say column A) count up: 1,2,3,4,5....1000. Then in cell A1001, I'd like the count to revert back to 1. I did a lot of searching and found the following formula:

A1: =(IF(MOD((ROW()-ROW(List_start)+1)/$D$1, 1)=0, 1, MOD((ROW()-ROW(List_start)+1)/$D$1, 1)))*$D$1

where D1 is the number i'd like to repeat, in my case, 1000. I can't get the formula to work. Any advice?

Thank you in advance.

Brian

 

[Rick Rothstein]

If you put the number 1 in A1 then you can put this formula in A2 and copy it down as far as you like...

=1+MOD(A1,1000)

 

[Foo_Man_Chu]

here's what I came up with:

=IF(A1=5,1,A1+1)

this would be the formula you put in the 2nd row of column "A" and copy it down and the number 5 is the limit of what you want to repeat, so in your case it would be 1000. of course, you would have to put 1 in A1.

hth

 

[Scott Huish]

In A1:

=MOD(ROW()-1,1000)+1

Copy down.

 

[Jinjunwei]

If you put the number 1 in A1 then you can put this formula in A2 and copy it down as far as you like...

=1+MOD(A1,1000)

Thanks! This worked perfectly.

 

[Jinjunwei]

In A1:

=MOD(ROW()-1,1000)+1

Copy down.

Got this one to work too. Thanks!

 

[Rick Rothstein]

In A1:

=MOD(ROW()-1,1000)+1

Copy down.

Wouldn't it be more efficient to simply put a 1 in the A1 and then use the simplified MOD formula I posted rather than burden the thousands of rows the OP hints will be involved with the extra function call (the ROW function)? With that said, I'm now thinking the formula Foo_Man_Chu posted would be better than either of our formulas as I would expect the IF function call to be more efficient for Excel to execute than the MOD function call.

 

[Jinjunwei]

HOTPEPPER, Foo, and Rick:

Thanks so much. I wanted to ask you guys a slight follow up question:

Say I wanted to create a similar list of numbers that put values in ascending 1000 increments. So, cell A1-A1000 would be 1000; A1001-A2000 would be 2000, etc. Is there an easy way to do this one? I tried looking around for this one as well.. but im not sure how to articulate this type of range.

Brian

 

[Rick Rothstein]

Staying with my original approach, you would put 1000 in A1 and this formula in A2...

=1+MOD(A1-2,1000)

and copy it down as needed. But going with Foo_Man_Chu's approach (which I still think may be the more efficient method), you would put this formula in A2...

=IF(A1=1,1000,A1-1)

and copy it down as needed.

 

[Jinjunwei]

Staying with my original approach, you would put 1000 in A1 and this formula in A2...

=1+MOD(A1-2,1000)

and copy it down as needed. But going with Foo_Man_Chu's approach (which I still think may be the more efficient method), you would put this formula in A2...

=IF(A1=1,1000,A1-1)

and copy it down as needed.

Hmm. I tired both of the above.. and just got a count down: a1: 1000, a2: 999, a3: 998. Maybe I made a mistake?