continue a sequence in excel

cakester
cakester used Ask the Experts™
on
I have a bunch of numbers  in a column in excel like this:

1
2
3
1
2
3

and I want to have about 1000 sets of 1,2,3. I tried selecting them and dragging them down but all I got was a bunch of weirdness like this

2.8
3.028571429
3.257142857
3.485714286
3.714285714
3.942857143
4.171428571
4.4

Two questions. Why does the drag down method not work here as I vaguely remember doing something similar in the past and it worked then?
How can I easily reproduce a lot of these 1,2,3's down the column
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
A formula like the following will produce your series:
=MOD(ROWS(A$1:A1)-1,3)+1
Top Expert 2010
Commented:
One very easy way...

Assuming you are starting in A1, then add these in A1:A3...

1
2
3

In A4, add this formula...

=A1

Copy the formula in A4 down through A3000 to get 1000 sets of these.  Replace with Copy / Paste Special / Values to convert the formulae to values if you wish.
Top Expert 2010

Commented:
slow fingers :)
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
matthewspatrick,

Hi,

Not sure what you mean about the paste special. anyway that sort of worked but I got a bunch of 1/1/1900 2/1/1900 3/1/1900 dates in te columns. I tried selecting the whole column then format cells then selected General or text but still same problem
1
2
3

Then, select those 3 cells and drag them down to however far you need.  When you do that, the numberes will be 1, 2, 3, 4, 5, 6, etc., and there will be an "Autofill Options" icon at the bottom of the fill.  (It looks like a couple of cells with a + in it.)  Click on it and select "Copy Cells".  That will change the whole series to 1, 2, 3, 1, 2, 3, 1, 2, 3, etc.

Hope that hels.
russellrichter

Author

Commented:

russellrichter,

That worked great and answered my other question which was what I was doing with my attempt to do it myself..


matthewspatrick,

Good attempt and I am sure you would have explained the dates I were getting. I am going to increase the general points slightly and award you a small amount as an assisted answer as you were quite prompt and nearly got it right.

Thanks to both of you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial