5
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.
Pandincus
Posted 2010-09-02T15:05:51.680
Reputation: 329
13
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.
Lunatik
Posted 2010-09-02T15:05:51.680
Reputation: 4 973
5
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.
Arjan
Posted 2010-09-02T15:05:51.680
Reputation: 29 084
I can't see how this works without using a + operator. – Tamara Wijsman – 2010-09-02T15:17:42.650
Still... How does it know to count up? What if I want it to count down? – Tamara Wijsman – 2010-09-02T15:21:53.627
Works in Excel 2003. Can't tell you why @Tom, but it seems to. Not for counting down, however. – DMA57361 – 2010-09-02T15:25:30.647
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. – Tamara Wijsman – 2010-09-02T15:27:52.030
@Tom, don't worry about the downvote, I already got the pity upvote :-). And indeed, for other sequences than 1-1-2-2-3-3 I guess adding a 3rd and 4th row with explicit formulas is to be preferred. – Arjan – 2010-09-02T15:31:14.737
Removed my answer, it's getting pretty duplicate with so much answers and the existing ones are better... At first I planned to include a video with my answer. But I made one for your method, just for the fun of trying it and it could be useful to use on Super User in the future: http://www.screencast.com/t/OTRjMzRh
– Tamara Wijsman – 2010-09-02T15:47:09.2075
For a pure formula version,
=ROUNDUP(ROW(A1)/2,0) will fill down correctly. (If starting further down than row 1, subtract as needed.)
Allen Gould
Posted 2010-09-02T15:05:51.680
Reputation: 151
2
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
DMA57361
Posted 2010-09-02T15:05:51.680
Reputation: 17 581
2
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.
MBraedley
Posted 2010-09-02T15:05:51.680
Reputation: 2 712
1
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 =)
Matt
Posted 2010-09-02T15:05:51.680
Reputation: 396
For display purposes, no need to paste as values, right? This works just fine for me (in OpenOffice.org). – Arjan – 2010-09-02T15:17:41.090
@Arjan Absolutely. – Lunatik – 2010-09-02T15:34:29.337