Filling number series in a pattern

[NoBob]

Hi,

Is there any way of getting the fill handle to continue with a number series as below (every two rows the same)? or some other way? Am on a Mac, using Excel 2016.

000170
000170
000171
000171
000172
000172
000173
000173

Thanks.

[Teblol]

You can just highlight those lines, select the little box in the bottom right corner of the last cell and drag as far down as you want.

[NoBob]

Thanks, Maybe I'm missing something, something I need to click?

It just repeats the same pattern. The numbers don't increase. In the list from above, the next pair should be

000174
000174

Thanks.

[Special-K]

I think that only works between 2 cells to establish a pattern. The cells 170 and 170 have no pattern hence the zero increment

Assuming this starts on row 1

in A1
=170+INT((ROW()-1)/2)
and copy down

Now select the entire range and Paste Values, overwriting the formula with the actual calculated values

[NoBob]

Thanks, That's nice, but am starting on row 435, and it shows 390 (I put 173 in the formula) as the result.

If I paste the formula you wrote into A1 on a new page, it works ok, but only shows as 170, rather than 000170.

[Special-K]

Try this

=TEXT(170+INT((ROWS(INDIRECT("1:"&ROW(A1:A100)))-1)/2),"000000")

This shouldnt matter where you put it, note the 170 starting position in the formula, change it if necessary

[hemesh]

a little shorter version may be amended as
=TEXT(170+INT((ROW(A1)-1)/2),"000000")

[shg]

Another way:

Row\Col	A	B
2	000170	A2: Input
3	000170	A3: =A2
4	000171	A4: =TEXT(A2+1, "000000")
5	000171
6	000172
7	000172
8	000173
9	000173
10	000174
11	000174
12	000175
13	000175
14	000176
15	000176
16	000177

Enter A2 as '000170 (note the leading apostrophe).

Enter the formulas shown in A3 and A4. Select both cells and drag down.

Change A2 as desired.

[NoBob]

Thanks Special-K and hemesh, they work great.

Can't get yours to work though shg. In A3 I just get a blue dot and a zero.

[hemesh]

@NoBob ! You are welcome!

All solutions works as same for your information, Please check once again.

Regards

[NoBob]

Hi, yeah, I was putting '000170 in A1 instead of A2

But now =TEXT(170+INT((ROWS(INDIRECT("1:"&ROW(A1:A100)))-1)/2),"000000") and =TEXT(170+INT((ROW(A1)-1)/2),"000000") have stopped working. I'm up to row 1017, and am substituting 605 for 170, i.e. =TEXT(605+INT((ROWS(INDIRECT("1:"&ROW(A1:A100)))-1)/2),"000000")

[FDibbins]

Here is another way....

1. in the 1st cell (say A1), enter 170
2. in the 2nd cell (say A2), enter 170
3. A3=A1+1
4. Custom format all those to 000000
5. Copy A3 down as far as needed

[NoBob]

Thank you!

[FDibbins]

Happy to help, thanks for the feedback