Build A Table By Unpivoting Different List To Get An Exhaustive List Of All The Unique Combinations

[ED38]

Hello,
I succeed to build the formula for column G and I but I have an issue with the formula in cell H4. I would like the sequence of 12 cells starting in H4 to be repeated 5 times (nb of countries).
I guess I am not far but trying for 2 hours now...
could you help?

Thanks you

Book6
	A	B	C	D	E	F	G	H	I
1	BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2	Count	5	4	3
3		Country	Product	Delivery		TABLE	Country	Product	Delivery
4		Australia	AAA	Cloud			Australia	AAA	Cloud
5		Chile	BBB	Off-Cloud			Australia	AAA	Off-Cloud
6		France	CCC	sub			Australia	AAA	sub
7		Japan	DDD				Australia	BBB	Cloud
8		United States					Australia	BBB	Off-Cloud
9							Australia	BBB	sub
10							Australia	CCC	Cloud
11							Australia	CCC	Off-Cloud
12							Australia	CCC	sub
13							Australia	DDD	Cloud
14							Australia	DDD	Off-Cloud
15							Australia	DDD	sub
16							Chile	#REF!	Cloud
17							Chile	#REF!	Off-Cloud
18							Chile	#REF!	sub
19							Chile	#REF!	Cloud
20							Chile	#REF!	Off-Cloud
21							Chile	#REF!	sub
22							Chile	#REF!	Cloud
23							Chile	#REF!	Off-Cloud
24							Chile	#REF!	sub
25							Chile	#REF!	Cloud
26							Chile	#REF!	Off-Cloud
27							Chile	#REF!	sub
28							France	#REF!	Cloud
29							France	#REF!	Off-Cloud
30							France	#REF!	sub
31							France	#REF!	Cloud
32							France	#REF!	Off-Cloud
33							France	#REF!	sub
34							France	#REF!	Cloud
35							France	#REF!	Off-Cloud
36							France	#REF!	sub
37							France	#REF!	Cloud
38							France	#REF!	Off-Cloud
39							France	#REF!	sub
40							Japan	#REF!	Cloud
41							Japan	#REF!	Off-Cloud
42							Japan	#REF!	sub
43							Japan	#REF!	Cloud
44							Japan	#REF!	Off-Cloud
45							Japan	#REF!	sub
46							Japan	#REF!	Cloud
47							Japan	#REF!	Off-Cloud
48							Japan	#REF!	sub
49							Japan	#REF!	Cloud
50							Japan	#REF!	Off-Cloud
51							Japan	#REF!	sub
52							United States	#REF!	Cloud
53							United States	#REF!	Off-Cloud
54							United States	#REF!	sub
55							United States	#REF!	Cloud
56							United States	#REF!	Off-Cloud
57							United States	#REF!	sub
58							United States	#REF!	Cloud
59							United States	#REF!	Off-Cloud
60							United States	#REF!	sub
61							United States	#REF!	Cloud
62							United States	#REF!	Off-Cloud
63							United States	#REF!	sub
Sheet1

Cell Formulas
Range	Formula
B2:D2	B2	=COUNTA(B3:B8)-1
G4:G63	G4	=LET(r,FILTER(OFFSET(B$4,0,0,B2,),OFFSET(B$4,0,0,B2,)<>""),cnt,C2*D2,INDEX(r,SEQUENCE(COUNTA(r) *cnt,,1,1/cnt)))
H4:H63	H4	=LET(r,FILTER(OFFSET(C$4,0,0,C2,),OFFSET(C$4,0,0,C2,)<>""),cnt,B2*D2,INDEX(r,SEQUENCE(COUNTA(r)*cnt,,1,1/D2)))
I4:I63	I4	=LET(r,FILTER(OFFSET(D$4,0,0,D2,),OFFSET(D$4,0,0,D2,)<>""),cnt,B2*C2,INDEX(r,MOD(SEQUENCE(COUNTA(r) *cnt,,0),COUNTA(r))+1))
Dynamic array formulas.

EDIT:
by the way would there be a way to do that with a single formula? (for the beauty of the exercice!)

 

[ED38]

Wow! Great, I am going to store it carefully in my library of special formulas.
Very Helpful, very valuable contribution.
Thanks a lot

 

[Fluff]

Glad we could help & thanks for the feedback.