Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

6 Answers

up vote 12 down vote accepted

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.

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
show 3 more comments

For a pure formula version,

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

share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.