Converting Multidimensional Arrays Into A Spill Range

Question

I've been posting a lot of Array/Spill Range answers lately trying to generate non-vba/App Script solutions. However, I've run into situations where I have arrays within arrays that aren't spilling as I expect. This specific question caused me to post this question.

Synopsis of issue I was addressing:

Starting values of 5 columns of data in A:E.
OP wants unique list of first four columns (A:D)
Transpose Column E values to the right.

Sample Starting Data:

Col A	B	C	D	E
Alpha	Bravo	Charlie	Delta	1
Alpha	Bravo	Charlie	Delta	2
Alpha	Bravo	Charlie	Delta	3
Delta	Echo	Foxtrot	Gulf	4
Delta	Echo	Foxtrot	Gulf	5
Delta	Echo	Foxtrot	Gulf	6
XRay	Yankee	Zulu	Delta	9
XRay	Yankee	Zulu	Delta	4
XRay	Yankee	Zulu	Delta	2
XRay	Yankee	Zulu	Delta	1
XRay	Yankee	Zulu	Delta	3
Alpha	Bravo	Charlie	Delta	4

Desired Outcome:

i	J	K	L	M	N	O	P	Q
Alpha	Bravo	Charlie	Delta	1	2	3	4
Delta	Echo	Foxtrot	Gulf	4	5	6
XRay	Yankee	Zulu	Delta	9	4	2	1	3

My solution spreadsheet is posted here or see google sheets version that you can edit in duplicate tabs.

I can easily get the first four columns using the below formula in cell i1

=UNIQUE(FILTER(A:D,NOT(ISBLANK((A:A)))))

I can then use the function below to create the transposed values to spill out to the right (as shown above) in M1.

=TRANSPOSE(FILTER(E:E,(NOT(ISBLANK(E:E))*(A:A&B:B&C:C&D:D=I1&J1&K1&L1))))

My challenge is getting this above formula to spill down to match the first four columns. Obviously, I can drag the formula down (which was my proposal), but how can I make this formula dynamic that spills down similar to the first four columns? The below formula seemed to me like it should work, but it throws an error:

=FILTER(TRANSPOSE(FILTER($E:$E,(NOT(ISBLANK($E:$E))*($A:$A&$B:$B&$C:$C&$D:$D=I1:I999&J1:J999&K1:K999&L1:L999)))),NOT(ISBLANK(L1:L999)))

Bonus/Similar question: I experienced a similar problem in this answer. If you can figure how how to make column E in this spreadsheet a dynamic spill range, I'll upvote the answer.

I saw that question, and worked on it for a couple hours before deciding it would not be possible without LAMBDA(). Array formula do not like the dual iteration. — Scott Craner
– Scott Craner, Commented Aug 12, 2021 at 19:21
Yes, LAMBDA is the answer. And BYROW (Insiders only atm, and also gears of Lambda) — chris neilsen
– chris neilsen, Commented Aug 14, 2021 at 21:27

P.b · Accepted Answer · 2021-08-14 20:45:56Z

1

=LET(data,FILTER(A:E,A:A<>""),
column1,UNIQUE(FILTER(A:D,A:A<>"")),
tableC1,FILTER(A:A&B:B&C:C&D:D,A:A<>""),
tableC2,FILTER(E:E,A:A<>""),
table,CHOOSE({1,2},tableC1,tableC2),
utableC1,TRANSPOSE(UNIQUE(tableC1)),
v,UNIQUE(TRANSPOSE(IF(tableC1=utableC1,INDEX(table,,2),"")),1),
seqrv,SEQUENCE(ROWS(v)),
x,MMULT(LEN(v)--(v<>""),SIGN(SEQUENCE(COLUMNS(v))))-1,
y,MMULT(--(TRANSPOSE(seqrv)<seqrv),x)+seqrv,
column2,IFERROR(MID(TEXTJOIN(" ",1,v),y,x),""),
IF(SEQUENCE(1,COLUMNS(data))<=COLUMNS(column1),column1,column2))

This results in the desired spill result, but the data of column E is joined in one cell instead of spilling to the right. I hope that's acceptable. The result would look like this using the function above:

PS partial credits to Mark Fitzgerald for posting this answer

answered Aug 14, 2021 at 20:45

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pgSystemTester Over a year ago

Thanks for the post. I upvoted but did not accept as the objective is indeed to maintain the spill range going out to the right, versus grouping them in a cell as you did in column k. It is cool that you were able to get everything into one cell. That'd be nice to have, but at this point, a satisfactory answer can have two formulas in two cells: i1 (in my original sheet which is working fine) and then somehow getting the formula in M1 to spill down while maintaining the spill range out to the right.

T.M. Over a year ago

Not for readibility, but for the effort: +:)

pgSystemTester Over a year ago

@T.M. yea I thought about chopping up the answer a bit with some photoshop as that's a lot to absorb! But it was indeed a good effort, and PB appears to be relatively new so don't want to be overly critical (I know my answers didn't win any beautiful/neat awards when my SO rating was 1,426😊 )

bricks96 · Accepted Answer · 2024-10-24 09:18:13Z

1

This formula will work with Google Sheets.

=BYROW(WRAPROWS(TOROW(UNIQUE(A:D),1),4),LAMBDA(r,HSTACK(r,TOROW(FILTER(E:E,A:A&B:B&C:C&D:D=JOIN(,r))))))

The empty rows below are handled within the formula, making it dynamic vertically; however, with the columns being concatenated like they are, it wouldn't be able to dynamically handle different number of columns unless you modify the formula.

answered Oct 24, 2024 at 9:18

bricks96

6954 silver badges8 bronze badges

1 Comment

pgSystemTester Over a year ago

I just saw this. Give me a second to validate solution. I upvoted just because I'm a nice person...

pgSystemTester · Accepted Answer · 2023-11-23 00:13:05Z

Over two years later, it occurred to me that with the newer functions, I can now answer my own question. The challenge is getting a dynamic horizontal range which I solved with MakeArray.

Unfortunately because google sheets and Excel use two function names to do the same thing, I couldn't get a dynamic formula for both (Sheets usessplit while Excel uses textsplit)

Each formula expects let parameters of the full range filtered by one column, a character to represent cutting data the last column number before transposing

Google Sheets

Sample file here

=LET(alldata,FILTER(A:E,A:A<>""),    zSplitter,"?", zColumnCount,4,
allNames,HSTACK(CHOOSECOLS(alldata,SEQUENCE(1,zColumnCount))),
zNames,UNIQUE(allNames),
keysFiltered, BYROW(zNames,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,eRow))),
keysFull,BYROW(allNames,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,eRow))),
splitNums,BYROW(keysFiltered,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,FILTER(CHOOSECOLS(alldata,zColumnCount+1),keysFull=eRow)))),
zElementCount,BYROW(splitNums,LAMBDA(eRow, LEN(eRow)-LEN(SUBSTITUTE(eRow,zSplitter,""))+1)), zMaxCol,MAX(zElementCount),
zSideArray,MAKEARRAY(ROWS(zNames),zMaxCol,LAMBDA(r,c,LET(tempRng,INDEX(splitNums,r,1),tempSplitRng,SPLIT(tempRng,zSplitter),IF(c>CHOOSEROWS(zElementCount,r),"",CHOOSECOLS(tempSplitRng,c))))),
HSTACK(zNames,zSideArray))

Excel

=LET(alldata,FILTER(A:E,A:A<>""),zSplitter,"?", zColumnCount,4,
allNames,HSTACK(CHOOSECOLS(alldata,SEQUENCE(1,zColumnCount))),
zNames,UNIQUE(allNames),
keysFiltered, BYROW(zNames,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,eRow))),
keysFull,BYROW(allNames,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,eRow))),
splitNums,BYROW(keysFiltered,LAMBDA(eRow,TEXTJOIN(zSplitter,TRUE,FILTER(CHOOSECOLS(alldata,zColumnCount+1),keysFull=eRow)))),
zElementCount,BYROW(splitNums,LAMBDA(eRow, LEN(eRow)-LEN(SUBSTITUTE(eRow,zSplitter,""))+1)), zMaxCol,MAX(zElementCount),
zSideArray,MAKEARRAY(ROWS(zNames),zMaxCol,LAMBDA(r,c,LET(tempRng,INDEX(splitNums,r,1),tempSplitRng,TEXTSPLIT(tempRng,zSplitter),IF(c>CHOOSEROWS(zElementCount,r),"",CHOOSECOLS(tempSplitRng,c))))),
HSTACK(zNames,zSideArray))

Collectives™ on Stack Overflow

Converting Multidimensional Arrays Into A Spill Range

Sample Starting Data:

Desired Outcome:

3 Answers 3

3 Comments

1 Comment

Google Sheets

Excel

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Sample Starting Data:

Desired Outcome:

3 Answers 3

3 Comments

1 Comment

Google Sheets

Excel

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related