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
EDIT:
by the way would there be a way to do that with a single formula? (for the beauty of the exercice!)
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!)

Last edited by a moderator: