# In Excel, how to fill down consecutive integers following a pattern 1, 1, 2, 2,

In Excel, I know how to use the "fill down" feature to populate a column of sequential numbers.

I would like to do this but following a pattern like:

``````1
1
2
2
3
3
``````

Does anyone know how to do this? I cannot figure it out. When I try to use the fill down feature it tries to calculate a trend over the values such as 3.3, 3.7, 4.2, 4.8, etc.

-

I don't think Excel includes the logic to do this, although I'll be happy to be proved wrong.

A workaround would be to use a formula copied down the column, then use PasteSpecial>Values to overwrite with the desired values.

``````   A       B        C
1  1
2  1
3  =A1+1
4  =A2+1
5  =A3+1
``````

etc.

-
For display purposes, no need to paste as values, right? This works just fine for me (in OpenOffice.org). – Arjan Sep 2 '10 at 15:17
@Arjan Absolutely. – Lunatik Sep 2 '10 at 15:34
Sorry, I never marked this answer as accepted! Great solution, thank you! – Pandincus Nov 22 '10 at 22:17

No Excel here, but in OpenOffice.org, filling down the following value and formula:

``````      A
1     1
2   =A1
``````

...gets me new values and formulas:

``````      A
1     1
2   =A1
3     2
4   =A3
5     3
6   =A5
7     4
8   =A7
``````

...which displays as you want it to.

-
I can't see how this works without using a + operator. – Tom Wijsman Sep 2 '10 at 15:17
@Tom, well, it surely does! :-) – Arjan Sep 2 '10 at 15:19
Still... How does it know to count up? What if I want it to count down? – Tom Wijsman Sep 2 '10 at 15:21
Works in Excel 2003. Can't tell you why @Tom, but it seems to. Not for counting down, however. – DMA57361 Sep 2 '10 at 15:25
Oh, I see. If you do this with a single cell it counts up, if you do it with two cells it counts up based on the second cell... Silly me. Argh, I can't upvote either unless the answer is edited... I should ask first next time. – Tom Wijsman Sep 2 '10 at 15:27

For a pure formula version,

=ROUNDUP(ROW(A1)/2,0) will fill down correctly. (If starting further down than row 1, subtract as needed.)

-

You could do the following. Bit heavy handed, but allows for you to do more advanced things in the `if` check to produce more obsure patterns.

After the first two manual entries it does not rely on you coping a "block" to get the repeated pattern. The if statement does if for you.

``````     A
1    1
2    1
3    =IF(A2==A1,A2+1,A2)
4    =IF(A3==A2,A3+1,A3)
etc
``````
-

I find that entering formulas can cause problems later on if you need to sort, so I prefer not to use them for column filling. Instead, after a few minutes of trial and error, I came up with the following:

``````    A
1   1
2
3   2
4
``````

Select the first 4 rows, and drag to fill. Now select the column, copy that, move to the second row, and do a paste special, selecting the add operation. Since empty cells are treated as zero, you will get the desired value after the source and destination cells are added.

-

Try this:

``````   a
1  1
2  1
3  =A1+1
4  =A2+1
``````

Then select A3 and A4 (both should highlight with the marching ants border). Carefully move your mouse cursor over one of the bottom corners of the selection (like the bottom right corner) and drag it downward.

...what TomWij said... sorry, I was slow on the response =)

-