Dynamic Arrary - Generate long table iterated through multiple columns using sequence

[zmjerry]

Hi everyone,

I'm so excited to join this group. I recently watched some videos on the dynamic array function in Excel and I think this is gonna change a lot of things. I'm looking forward to learn more from all of you.

Here is my problem:

As the pictures show, Table 1 is a simple =SEQUENCE(5,1,0,1). Table 2 is a manual table that would get added more assets and products to it.

I want to have a dynamic array to repeat 0-4 for each row in Table 2, by inputting just one formula in H3.

I'm able to do this in Power Query by merging unrelated columns in Table 1 and Table 2, and expand it. But I need to do this in the worksheets instead.

Any help or discussion is appreciated.

Thanks,
MJ

 

[Fluff]

How about two formulae

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2					Name	Product
3			0		Asset 1	A		0	Asset 1	A
4			1		Asset 1	B		1	Asset 1	A
5			2		Asset 2	C		2	Asset 1	A
6			3		Asset 2	D		3	Asset 1	A
7			4					4	Asset 1	A
8								0	Asset 1	B
9								1	Asset 1	B
10								2	Asset 1	B
11								3	Asset 1	B
12								4	Asset 1	B
13								0	Asset 2	C
14								1	Asset 2	C
15								2	Asset 2	C
16								3	Asset 2	C
17								4	Asset 2	C
18								0	Asset 2	D
19								1	Asset 2	D
20								2	Asset 2	D
21								3	Asset 2	D
22								4	Asset 2	D
23
Main

Cell Formulas
Range	Formula
C3:C7	C3	=SEQUENCE(5,,0)
H3:H22	H3	=INDEX(C3#,MOD(SEQUENCE(ROWS(C3#)*ROWS(Table11))-1,ROWS(C3#))+1)
I3:J22	I3	=INDEX(Table11,SEQUENCE(ROWS(C3#)*ROWS(Table11),,1,1/ROWS(C3#)),{1,2})
Dynamic array formulas.

 

[zmjerry]

It works!! This is magical.

 

[Fluff]

You're welcome & thanks for the feedback.