Drag formula down column to show consecutive large numbers as they vary...

[Dan10]

Can someone please provide an Excel formula to do this using one or more of the lookup Columns 1, 2 and/or 3 so the large numbers are repeated below each where the ''1s' or '1 to 9s' appear in Columns 2 or 3 respectively - Formula in Column4? (the following is just a sample. I would like all the numbers in Column1 to do this eventually...

Column1 Lookup	Column2	Column3	Column4
106	106	106	Formula
668	1	1	Formula
1964	1	2	Formula
4221	1	3	Formula
5771	1	4	Formula
7641	1	5	Formula
9842	1	6	Formula
12410	1	7	Formula
15350	1	8	Formula
18700	1	9	Formula
26674	668	668	Formula
	1	1	Formula
	1	2	Formula
	1	3	Formula
	1	4	Formula
	1	5	Formula
	1	6	Formula
	1	7	Formula
	1964	1964	Formula
	1	1	Formula
	1	2	Formula
	1	3	Formula
	1	4	Formula
	1	5	Formula
	1	6	Formula

 

[Cubist]

I don't have a drag down solution, but here's an array option.

Book1
	A	B	C	D
1	106		106	106
2	668		1	1
3	1964		1	2
4	4221		1	3
5	5771		1	4
6	7641		1	5
7	9842		1	6
8	12410		1	7
9	15350		1	8
10	18700		1	9
11	26674		106	106
12			1	1
13			1	2
14			1	3
15			1	4
16			1	5
17			1	6
18			1	7
19			1	8
20			1	9
21			668	668
22			1	1
23			1	2
24			1	3
25			1	4
26			1	5
27			1	6
28			1	7
29			1	8
30			1	9
31			1964	1964
32			1	1
33			1	2
34			1	3
35			1	4
36			1	5
37			1	6
38			1	7
39			1	8
40			1	9
Sheet1

Cell Formulas
Range	Formula
C1:C120	C1	=LET(s,SEQUENCE(SUM(SEQUENCE(ROWS(A1:A11)+1,,10,0)),,0),d,SCAN(1,SEQUENCE(ROWS(A1:A11),,10,0),LAMBDA(a,b,SUM(a,b))),x,XLOOKUP(s,d,A1:A11,,1),IF(MOD(s,10)<>0,1,x))
D1:D120	D1	=LET(s,SEQUENCE(SUM(SEQUENCE(ROWS(A1:A11)+1,,10,0)),,0),d,SCAN(1,SEQUENCE(ROWS(A1:A11),,10,0),LAMBDA(a,b,SUM(a,b))),x,XLOOKUP(s,d,A1:A11,,1),IF(MOD(s,10)=0,x,MOD(s,10)))
Dynamic array formulas.

 

[JEC]

One dynamic formula

Book1
	A	B	C	D
1	106		106	106
2	668		1	1
3	1964		1	2
4	4221		1	3
5	5771		1	4
6	7641		1	5
7	9842		1	6
8	12410		1	7
9	15350		1	8
10	18700		1	9
11	26674		668	668
12			1	1
13			1	2
14			1	3
15			1	4
16			1	5
17			1	6
18			1	7
19			1	8
20			1	9
21			1964	1964
22			1	1
23			1	2
24			1	3
25			1	4
26			1	5
27			1	6
28			1	7
29			1	8
30			1	9
31			4221	4221
Sheet1

Cell Formulas
Range	Formula
C1:D110	C1	=LET(s,SEQUENCE(9),z,s^0,DROP(REDUCE("",A1:A11,LAMBDA(a,b,VSTACK(a,IFERROR(VSTACK(b,HSTACK(z,s)),b)))),1))
Dynamic array formulas.

 

[Dan10]

Thank you for your reply. I dont think the formula is doing what I was expecting, probably because I did not explain very well. I need the formula to copy 106 down to the next large number (1964), then 1964 down to the next, so that the large numbers (106, 668 etc) are repeated in the one column. The formula looks to place the original column/s in which I perhaps wrongly showed and/or did not show an example of the result for clarity?! So, the result should look like this:

106​

106​

106​

106​

106​

106​

106​

106​

106​

106​

668​

668​

668​

668​

668​

668​

668​

 

[Dan10]

Thank you. I have replied to the second post (above). The formula did not do what I was hoping, probably as I did not explain very well?!

 

[Cubist]

The question wasn't clear. We thought you want to go from A-> B and C. Is this what you're looking for?

Book1
	A	B	C	D	E
1	106	106		106
2	668	1		106
3	1964	1		106
4	4221	1		106
5	5771	1		106
6	7641	1		106
7	9842	1		106
8	12410	1		106
9	15350	1		106
10	18700	1		106
11	26674	668		668
12		1		668
13		1		668
14		1		668
15		1		668
16		1		668
17		1		668
18		1		668
19		1964		1964
20		1		1964
21		1		1964
22		1		1964
23		1		1964
24		1		1964
25		1		1964
Sheet3

Cell Formulas
Range	Formula
D1:D25	D1	=SCAN(1,B1:B25,LAMBDA(a,b,IF(b=1,a,b)))
Dynamic array formulas.

 

[Dan10]

Thats it, thank you!