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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

For a pure formula version,

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

Answer 4

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

Answer 5

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.

Answer 6

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 =)