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

E

ED38

New Member
Joined
Mar 29, 2024
Messages
38
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHI
1BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2Count543
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16Chile#REF!Cloud
17Chile#REF!Off-Cloud
18Chile#REF!sub
19Chile#REF!Cloud
20Chile#REF!Off-Cloud
21Chile#REF!sub
22Chile#REF!Cloud
23Chile#REF!Off-Cloud
24Chile#REF!sub
25Chile#REF!Cloud
26Chile#REF!Off-Cloud
27Chile#REF!sub
28France#REF!Cloud
29France#REF!Off-Cloud
30France#REF!sub
31France#REF!Cloud
32France#REF!Off-Cloud
33France#REF!sub
34France#REF!Cloud
35France#REF!Off-Cloud
36France#REF!sub
37France#REF!Cloud
38France#REF!Off-Cloud
39France#REF!sub
40Japan#REF!Cloud
41Japan#REF!Off-Cloud
42Japan#REF!sub
43Japan#REF!Cloud
44Japan#REF!Off-Cloud
45Japan#REF!sub
46Japan#REF!Cloud
47Japan#REF!Off-Cloud
48Japan#REF!sub
49Japan#REF!Cloud
50Japan#REF!Off-Cloud
51Japan#REF!sub
52United States#REF!Cloud
53United States#REF!Off-Cloud
54United States#REF!sub
55United States#REF!Cloud
56United States#REF!Off-Cloud
57United States#REF!sub
58United States#REF!Cloud
59United States#REF!Off-Cloud
60United States#REF!sub
61United States#REF!Cloud
62United States#REF!Off-Cloud
63United States#REF!sub
Sheet1
Cell Formulas
RangeFormula
B2:D2B2=COUNTA(B3:B8)-1
G4:G63G4=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:H63H4=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:I63I4=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:
Sort by date Sort by votes
How about
Fluff.xlsm
ABCDEFGHI
1
2Count543
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16ChileAAACloud
17ChileAAAOff-Cloud
18ChileAAAsub
19ChileBBBCloud
20ChileBBBOff-Cloud
21ChileBBBsub
22ChileCCCCloud
23ChileCCCOff-Cloud
24ChileCCCsub
25ChileDDDCloud
26ChileDDDOff-Cloud
27ChileDDDsub
28FranceAAACloud
29FranceAAAOff-Cloud
30FranceAAAsub
31FranceBBBCloud
32FranceBBBOff-Cloud
33FranceBBBsub
34FranceCCCCloud
35FranceCCCOff-Cloud
36FranceCCCsub
37FranceDDDCloud
38FranceDDDOff-Cloud
39FranceDDDsub
40JapanAAACloud
41JapanAAAOff-Cloud
42JapanAAAsub
43JapanBBBCloud
44JapanBBBOff-Cloud
45JapanBBBsub
46JapanCCCCloud
47JapanCCCOff-Cloud
48JapanCCCsub
49JapanDDDCloud
50JapanDDDOff-Cloud
51JapanDDDsub
52United StatesAAACloud
53United StatesAAAOff-Cloud
54United StatesAAAsub
55United StatesBBBCloud
56United StatesBBBOff-Cloud
57United StatesBBBsub
58United StatesCCCCloud
59United StatesCCCOff-Cloud
60United StatesCCCsub
61United StatesDDDCloud
62United StatesDDDOff-Cloud
63United StatesDDDsub
64
Sheet6
Cell Formulas
RangeFormula
B2:D2B2=COUNTA(B3:B8)-1
G4:I63G4=LET(a,INDEX(B4:D10,MID(BASE(SEQUENCE(B2^3,,0),B2,3),SEQUENCE(,3),1)+1,MOD(SEQUENCE(,3,0),3)+1),FILTER(a,(INDEX(a,,3)<>"")*(INDEX(a,,2)<>"")))
Dynamic array formulas.
 
Upvote 0
Solution
too slow...
MrExcelPlayground24.xlsx
ABCDEFGHI
1BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2Count
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16ChileAAACloud
17ChileAAAOff-Cloud
18ChileAAAsub
19ChileBBBCloud
20ChileBBBOff-Cloud
21ChileBBBsub
22ChileCCCCloud
23ChileCCCOff-Cloud
24ChileCCCsub
25ChileDDDCloud
26ChileDDDOff-Cloud
27ChileDDDsub
28FranceAAACloud
29FranceAAAOff-Cloud
30FranceAAAsub
31FranceBBBCloud
32FranceBBBOff-Cloud
33FranceBBBsub
34FranceCCCCloud
35FranceCCCOff-Cloud
36FranceCCCsub
37FranceDDDCloud
38FranceDDDOff-Cloud
39FranceDDDsub
40JapanAAACloud
41JapanAAAOff-Cloud
42JapanAAAsub
43JapanBBBCloud
44JapanBBBOff-Cloud
45JapanBBBsub
46JapanCCCCloud
47JapanCCCOff-Cloud
48JapanCCCsub
49JapanDDDCloud
50JapanDDDOff-Cloud
51JapanDDDsub
52United StatesAAACloud
53United StatesAAAOff-Cloud
54United StatesAAAsub
55United StatesBBBCloud
56United StatesBBBOff-Cloud
57United StatesBBBsub
58United StatesCCCCloud
59United StatesCCCOff-Cloud
60United StatesCCCsub
61United StatesDDDCloud
62United StatesDDDOff-Cloud
63United StatesDDDsub
Sheet5
Cell Formulas
RangeFormula
G4:I63G4=LET(country,B4:B8,prod,C4:C7,deli,D4:D6,x,ROWS(country),y,ROWS(prod),z,ROWS(deli),cnt,x*y*z,a,SEQUENCE(cnt,1,0,1),b,MOD(a,z)+1,c,MOD(INT(a/z),y)+1,d,MOD(INT(a/z/y),y*x)+1,e,INDEX(country,d),f,INDEX(prod,c),g,INDEX(deli,b),HSTACK(e,f,g))
Dynamic array formulas.
 
Upvote 0
Fluff said:
How about
Fluff.xlsm
ABCDEFGHI
1
2Count543
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16ChileAAACloud
17ChileAAAOff-Cloud
18ChileAAAsub
19ChileBBBCloud
20ChileBBBOff-Cloud
21ChileBBBsub
22ChileCCCCloud
23ChileCCCOff-Cloud
24ChileCCCsub
25ChileDDDCloud
26ChileDDDOff-Cloud
27ChileDDDsub
28FranceAAACloud
29FranceAAAOff-Cloud
30FranceAAAsub
31FranceBBBCloud
32FranceBBBOff-Cloud
33FranceBBBsub
34FranceCCCCloud
35FranceCCCOff-Cloud
36FranceCCCsub
37FranceDDDCloud
38FranceDDDOff-Cloud
39FranceDDDsub
40JapanAAACloud
41JapanAAAOff-Cloud
42JapanAAAsub
43JapanBBBCloud
44JapanBBBOff-Cloud
45JapanBBBsub
46JapanCCCCloud
47JapanCCCOff-Cloud
48JapanCCCsub
49JapanDDDCloud
50JapanDDDOff-Cloud
51JapanDDDsub
52United StatesAAACloud
53United StatesAAAOff-Cloud
54United StatesAAAsub
55United StatesBBBCloud
56United StatesBBBOff-Cloud
57United StatesBBBsub
58United StatesCCCCloud
59United StatesCCCOff-Cloud
60United StatesCCCsub
61United StatesDDDCloud
62United StatesDDDOff-Cloud
63United StatesDDDsub
64
Sheet6
Cell Formulas
RangeFormula
B2:D2B2=COUNTA(B3:B8)-1
G4:I63G4=LET(a,INDEX(B4:D10,MID(BASE(SEQUENCE(B2^3,,0),B2,3),SEQUENCE(,3),1)+1,MOD(SEQUENCE(,3,0),3)+1),FILTER(a,(INDEX(a,,3)<>"")*(INDEX(a,,2)<>"")))
Dynamic array formulas.
Click to expand...
Thanks a lot Fluff. once again! I suspect you are waiting for my requests :)
I fine tuned it to make it even more generic but need to understand the last part '*(INDEX(a,,2)<>"")' : why '2' ? is it the number of column minus 1 or?
thank you

Book6
ABCDEFGHI
1BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2Count543
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16ChileAAACloud
17ChileAAAOff-Cloud
18ChileAAAsub
19ChileBBBCloud
20ChileBBBOff-Cloud
21ChileBBBsub
22ChileCCCCloud
23ChileCCCOff-Cloud
24ChileCCCsub
25ChileDDDCloud
26ChileDDDOff-Cloud
27ChileDDDsub
28FranceAAACloud
29FranceAAAOff-Cloud
30FranceAAAsub
31FranceBBBCloud
32FranceBBBOff-Cloud
33FranceBBBsub
34FranceCCCCloud
35FranceCCCOff-Cloud
36FranceCCCsub
37FranceDDDCloud
38FranceDDDOff-Cloud
39FranceDDDsub
40JapanAAACloud
41JapanAAAOff-Cloud
42JapanAAAsub
43JapanBBBCloud
44JapanBBBOff-Cloud
45JapanBBBsub
46JapanCCCCloud
47JapanCCCOff-Cloud
48JapanCCCsub
49JapanDDDCloud
50JapanDDDOff-Cloud
51JapanDDDsub
52United StatesAAACloud
53United StatesAAAOff-Cloud
54United StatesAAAsub
55United StatesBBBCloud
56United StatesBBBOff-Cloud
57United StatesBBBsub
58United StatesCCCCloud
59United StatesCCCOff-Cloud
60United StatesCCCsub
61United StatesDDDCloud
62United StatesDDDOff-Cloud
63United StatesDDDsub
Sheet1
Cell Formulas
RangeFormula
B2:D2B2=COUNTA(B3:B8)-1
G4:I63G4=LET(col,COLUMNS($B$3:$D$3),a,INDEX(OFFSET($B$4,0,0,MAX(OFFSET($B$2,0,0,,col)),col),MID(BASE(SEQUENCE($B$2^col,,0),B2,col),SEQUENCE(,col),1)+1,MOD(SEQUENCE(,col,0),col)+1),FILTER(a,(INDEX(a,,col)<>"")*(INDEX(a,,2)<>"")))
Dynamic array formulas.
 
Upvote 0
ED38 said:
need to understand the last part '*(INDEX(a,,2)<>"")
Click to expand...
It's returning the 2nd column of the array a.
There is no need to use Offset which is volatile (and could slow things down), as you can see from the formula I posted you can set the range larger than you need.
Also as the formula is in a single cell there is no need to make the cell references absolute.
 
Upvote 0
JamesCanale said:
too slow...
MrExcelPlayground24.xlsx
ABCDEFGHI
1BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2Count
3CountryProductDeliveryTABLECountryProductDelivery
4AustraliaAAACloudAustraliaAAACloud
5ChileBBBOff-CloudAustraliaAAAOff-Cloud
6FranceCCCsubAustraliaAAAsub
7JapanDDDAustraliaBBBCloud
8United StatesAustraliaBBBOff-Cloud
9AustraliaBBBsub
10AustraliaCCCCloud
11AustraliaCCCOff-Cloud
12AustraliaCCCsub
13AustraliaDDDCloud
14AustraliaDDDOff-Cloud
15AustraliaDDDsub
16ChileAAACloud
17ChileAAAOff-Cloud
18ChileAAAsub
19ChileBBBCloud
20ChileBBBOff-Cloud
21ChileBBBsub
22ChileCCCCloud
23ChileCCCOff-Cloud
24ChileCCCsub
25ChileDDDCloud
26ChileDDDOff-Cloud
27ChileDDDsub
28FranceAAACloud
29FranceAAAOff-Cloud
30FranceAAAsub
31FranceBBBCloud
32FranceBBBOff-Cloud
33FranceBBBsub
34FranceCCCCloud
35FranceCCCOff-Cloud
36FranceCCCsub
37FranceDDDCloud
38FranceDDDOff-Cloud
39FranceDDDsub
40JapanAAACloud
41JapanAAAOff-Cloud
42JapanAAAsub
43JapanBBBCloud
44JapanBBBOff-Cloud
45JapanBBBsub
46JapanCCCCloud
47JapanCCCOff-Cloud
48JapanCCCsub
49JapanDDDCloud
50JapanDDDOff-Cloud
51JapanDDDsub
52United StatesAAACloud
53United StatesAAAOff-Cloud
54United StatesAAAsub
55United StatesBBBCloud
56United StatesBBBOff-Cloud
57United StatesBBBsub
58United StatesCCCCloud
59United StatesCCCOff-Cloud
60United StatesCCCsub
61United StatesDDDCloud
62United StatesDDDOff-Cloud
63United StatesDDDsub
Sheet5
Cell Formulas
RangeFormula
G4:I63G4=LET(country,B4:B8,prod,C4:C7,deli,D4:D6,x,ROWS(country),y,ROWS(prod),z,ROWS(deli),cnt,x*y*z,a,SEQUENCE(cnt,1,0,1),b,MOD(a,z)+1,c,MOD(INT(a/z),y)+1,d,MOD(INT(a/z/y),y*x)+1,e,INDEX(country,d),f,INDEX(prod,c),g,INDEX(deli,b),HSTACK(e,f,g))
Dynamic array formulas.
Click to expand...

Thank you a lot JamesCanale as well.
it also works perfectly.
A different approach I would have to learn about.
However I prefer Fluff's proposal as I find it easier to replicate in a more generic way, more flexible when the size of the list are moving, so sorry I will keep his proposal.
next time maybe ;-)
 
Upvote 0
Fluff said:
It's returning the 2nd column of the array a.
There is no need to use Offset which is volatile (and could slow things down), as you can see from the formula I posted you can set the range larger than you need.
Also as the formula is in a single cell there is no need to make the cell references absolute.
Click to expand...
Ok, I guess I got it, meaning if I have 4 lists to combine, it would mean this :
About offset, as I am facing lists with size that can vary a lot, it is the best way I found to make sure I am not missing data. Happy to hear about other options. (I suspect selecting the whole column would slow down even worse or?

Book6
ABCDEFGHIJ
1BUILD A TABLE BY UNPIVOTING DIFFERENT LIST TO GET AN EXHAUSTIF LIST OF ALL THE UNIQUE COMBINATIONS
2Count4432
3CountryProductDeliveryTypeTABLECountryProductDeliveryType
4AustraliaAAACloudXAustraliaAAACloudX
5ChileBBBOff-CloudYAustraliaAAACloudY
6FranceCCCsubAustraliaAAAOff-CloudX
7JapanDDDAustraliaAAAOff-CloudY
8AustraliaAAAsubX
9AustraliaAAAsubY
10AustraliaBBBCloudX
11AustraliaBBBCloudY
12AustraliaBBBOff-CloudX
13AustraliaBBBOff-CloudY
14AustraliaBBBsubX
15AustraliaBBBsubY
16AustraliaCCCCloudX
17AustraliaCCCCloudY
18AustraliaCCCOff-CloudX
19AustraliaCCCOff-CloudY
20AustraliaCCCsubX
21AustraliaCCCsubY
22AustraliaDDDCloudX
23AustraliaDDDCloudY
24AustraliaDDDOff-CloudX
25AustraliaDDDOff-CloudY
26AustraliaDDDsubX
27AustraliaDDDsubY
28ChileAAACloudX
29ChileAAACloudY
30ChileAAAOff-CloudX
31ChileAAAOff-CloudY
32ChileAAAsubX
33ChileAAAsubY
34ChileBBBCloudX
35ChileBBBCloudY
36ChileBBBOff-CloudX
37ChileBBBOff-CloudY
38ChileBBBsubX
39ChileBBBsubY
40ChileCCCCloudX
41ChileCCCCloudY
42ChileCCCOff-CloudX
43ChileCCCOff-CloudY
44ChileCCCsubX
45ChileCCCsubY
46ChileDDDCloudX
47ChileDDDCloudY
48ChileDDDOff-CloudX
49ChileDDDOff-CloudY
50ChileDDDsubX
51ChileDDDsubY
52FranceAAACloudX
53FranceAAACloudY
54FranceAAAOff-CloudX
55FranceAAAOff-CloudY
56FranceAAAsubX
57FranceAAAsubY
58FranceBBBCloudX
59FranceBBBCloudY
60FranceBBBOff-CloudX
61FranceBBBOff-CloudY
62FranceBBBsubX
63FranceBBBsubY
64FranceCCCCloudX
65FranceCCCCloudY
66FranceCCCOff-CloudX
67FranceCCCOff-CloudY
68FranceCCCsubX
69FranceCCCsubY
70FranceDDDCloudX
71FranceDDDCloudY
72FranceDDDOff-CloudX
73FranceDDDOff-CloudY
74FranceDDDsubX
75FranceDDDsubY
76JapanAAACloudX
77JapanAAACloudY
78JapanAAAOff-CloudX
79JapanAAAOff-CloudY
80JapanAAAsubX
81JapanAAAsubY
82JapanBBBCloudX
83JapanBBBCloudY
84JapanBBBOff-CloudX
85JapanBBBOff-CloudY
86JapanBBBsubX
87JapanBBBsubY
88JapanCCCCloudX
89JapanCCCCloudY
90JapanCCCOff-CloudX
91JapanCCCOff-CloudY
92JapanCCCsubX
93JapanCCCsubY
94JapanDDDCloudX
95JapanDDDCloudY
96JapanDDDOff-CloudX
97JapanDDDOff-CloudY
98JapanDDDsubX
99JapanDDDsubY
Sheet1
Cell Formulas
RangeFormula
B2:E2B2=COUNTA(B3:B8)-1
G4:J99G4=LET(col,COLUMNS($B$4:$E$10),a,INDEX($B$4:$E$10,MID(BASE(SEQUENCE($B$2^col,,0),$B$2,col),SEQUENCE(,col),1)+1,MOD(SEQUENCE(,col,0),col)+1),FILTER(a,(INDEX(a,,col)<>"")*(INDEX(a,,3)<>"")*(INDEX(a,,2)<>"")))
Dynamic array formulas.
 
Upvote 0
There's no need to select the entire column, you can just use a range that is larger than you would ever need.
Also Offset will always be A) Slow & B) Calculate far more often than is needed.

If you are going to have a varying number of columns you could use
Excel Formula: 
=LET(col,COLUMNS(B4:E4),a,INDEX(B4:E1000,MID(BASE(SEQUENCE(B2^col,,0),B2,col),SEQUENCE(,col),1)+1,MOD(SEQUENCE(,col,0),col)+1),FILTER(a,BYROW(a,LAMBDA(br,SUM(--(br=""))=0))))
 
Upvote 0
You could also make it more dynamic like
Fluff.xlsm
ABCDEFGHIJK
1
2Count4432
3CountryProductDeliveryTypeTABLECountryProductDeliveryType
4AustraliaAAACloudXAustraliaAAACloudX
5ChileBBBOff-CloudYAustraliaAAACloudY
6FranceCCCsubAustraliaAAAOff-CloudX
7JapanDDDAustraliaAAAOff-CloudY
8AustraliaAAAsubX
9AustraliaAAAsubY
10AustraliaBBBCloudX
11AustraliaBBBCloudY
12AustraliaBBBOff-CloudX
13AustraliaBBBOff-CloudY
14AustraliaBBBsubX
15AustraliaBBBsubY
16AustraliaCCCCloudX
17AustraliaCCCCloudY
18AustraliaCCCOff-CloudX
19AustraliaCCCOff-CloudY
20AustraliaCCCsubX
21AustraliaCCCsubY
22AustraliaDDDCloudX
23AustraliaDDDCloudY
24AustraliaDDDOff-CloudX
25AustraliaDDDOff-CloudY
26AustraliaDDDsubX
27AustraliaDDDsubY
28ChileAAACloudX
29ChileAAACloudY
30ChileAAAOff-CloudX
Sheet6
Cell Formulas
RangeFormula
B2:E2B2=COUNTA(B3:B8)-1
H4:K99H4=LET(data,TRIMRANGE(A4:G1000),col,COLUMNS(data),rws,ROWS(data),a,INDEX(data,MID(BASE(SEQUENCE(rws^col,,0),rws,col),SEQUENCE(,col),1)+1,MOD(SEQUENCE(,col,0),col)+1),FILTER(a,BYROW(a,LAMBDA(br,SUM(--(br=""))=0))))
Dynamic array formulas.
 
Upvote 0
Realised the mod function is not doing anything & so the formula can be simplified slightly
Excel Formula: 
=LET(data,TRIMRANGE(A4:G1000),col,COLUMNS(data),rws,ROWS(data),a,INDEX(data,MID(BASE(SEQUENCE(rws^col,,0),rws,col),SEQUENCE(,col),1)+1,SEQUENCE(,col)),FILTER(a,BYROW(a,LAMBDA(br,SUM(--(br=""))=0))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

tezza
  • Solved
Dynamically extend list help
Replies
3
Views
221
Cubist
Cubist
L
  • Solved
Split Text String based on listing
2 3 4
Replies
30
Views
3K
Lenard
L
S
  • Solved
Extract unique values from multiple columns based on a set criteria to create a running list
Replies
7
Views
2K
Eric W
Eric W
P
  • Question Question
Advanced Filter / Shared Workbook - workaround?
Replies
6
Views
4K
PureBluff
P
P
  • Question Question
Macro causes Excel to exit / attempt auto recover - however works fist time after re-opening?
Replies
7
Views
1K
PureBluff
P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top