Help needed with pattern

[Strider1024]

Ok so I want to get excel to autocomplete a pattern for me but have no idea how to set the correct formula...

What I need to have is this in a single column :

Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0

Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad1
Numpad1

Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad2
Numpad2

Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad3
Numpad3


So we have 8 sets of numpad0 per set, The last 2 need to go up in increments of 1 as above.. Upon hitting 9 row 7 & 8 need to go back to 0 and 5&6 need to goto 1

Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad0
Numpad9
Numpad9

Numpad0
Numpad0
Numpad0
Numpad0
Numpad1
Numpad1
Numpad0
Numpad0

Numpad0
Numpad0
Numpad0
Numpad0
Numpad1
Numpad1
Numpad1
Numpad1

Numpad0
Numpad0
Numpad0
Numpad0
Numpad1
Numpad1
Numpad2
Numpad2

This needs to occur all the way up until all are 9's.

Sorry if its hard to understand.. Anyone have an idea?

** EDIT** I have put spacers between the sets so it can be seen easier (I do not need those spacers there)

[Pete_UK]

I'll take a look at this tomorrow if nobody else has answered.

Pete

[FlameRetired]

Hello, Strider, and welcome to the Forum.

I'm waiting a bit longer. I don't think Excel 2010 has enough rows to pull this one off in a single column. Maybe in a future version?
The number I come up with is 41,478,480 rows. It could be more.

80 rows for the first set (including blanks). Then 80 x 80 for the next set, 80 x 80 x 80 for the next and 80 x 80 x 80 x 80 for the last. The last set by itself will take almost 41 million rows.

Edit: Sorry. My bad ... it's powers of 90 rows. It's worse than I thought.

[jhren]

In any row 1 cell...
=IF(MOD(ROW(),9)=0,"","Numpad"&MID(TEXT(INT((ROW()-1-INT(ROW()/9))/8),"0000"),MOD(INT((ROW()-1-INT(ROW()/9))/2),4)+1,1))
...and copy down until you get all 9's in the set.

NOTE: I did not see the edit at the end of your post until after I developed this formula... so spaces are included.

[jhren]

@FlameRetired

It appears each set has 4 duplicates, so that cuts the number of sets from 1E+8 down to 1E+4. 10,000 sets at 9 rows per set is only 90,000.

[FDibbins]

jhren, that's pretty slick

However, it seems to get stuck from about row 1 047 610. It gets to 1-1/1-1/6-6/4-4, then repeats that...

	F
1047592	Numpad1
1047593	Numpad1
1047594	Numpad1
1047595	Numpad1
1047596	Numpad6
1047597	Numpad6
1047598	Numpad3
1047599	Numpad3
1047600
1047601	Numpad1
1047602	Numpad1
1047603	Numpad1
1047604	Numpad1
1047605	Numpad6
1047606	Numpad6
1047607	Numpad4
1047608	Numpad4
1047609
1047610	Numpad1
1047611	Numpad1
1047612	Numpad1
1047613	Numpad1
1047614	Numpad6
1047615	Numpad6
1047616	Numpad4
1047617	Numpad4
1047618
1047619	Numpad1
1047620	Numpad1
1047621	Numpad1
1047622	Numpad1
1047623	Numpad6
1047624	Numpad6
1047625	Numpad4
1047626	Numpad4
1047627
1047628	Numpad1
1047629	Numpad1
1047630	Numpad1
1047631	Numpad1
1047632	Numpad6
1047633	Numpad6
1047634	Numpad4
1047635	Numpad4
1047636
1047637	Numpad1
1047638	Numpad1
1047639	Numpad1
1047640	Numpad1
1047641	Numpad6
1047642	Numpad6
1047643	Numpad4
1047644	Numpad4

really good work though

[jhren]

Ford,

Can't explain it offhand, but he should only have to go to row 89999.

***I think I know what's happening. I specified text (value,"0000") but after you go past what generated 4 digit number, you start getting 5 digit numbers, while the formula extracts the 1st, 2nd, 3rd, and 4th character from the text.

[FDibbins]

Can't explain it offhand, but he should only have to go to row 89999.

yup, about there...

	F
89992	Numpad9
89993	Numpad9
89994	Numpad9
89995	Numpad9
89996	Numpad9
89997	Numpad9
89998	Numpad9
89999	Numpad9

I just copied that aaallll the way to the last row

[FlameRetired]

@Ford I thought that, too at first.

@jhren Is my face red. And yes when I do your formula it goes to rows 89992-89999 were 99999999 is the result.

Edit: Forgot to refresh. Wanted to make triple sure this time. BTW: jhren cool formula.

[shg]

Row\Col	A	B
1	Sequence
2	0	A2: =MOD(INT(10*INT((ROWS(A$2:A2)-1)/4) / 10^(4-MOD(ROWS(A$2:A2) - 1,4))), 10)
3	0
4	0
5	0
6	0
7	0
8	0
9	1
10	0
11	0
12	0
13	2
14	0
15	0
16	0
17	3
18	0
19	0
20	0
21	4
22	0
23	0
24	0
25	5

Your pattern just duplicates each row of the pattern above, which I will leave to you.

EDIT: I didn't try jhren's formula, but it looks good.