Converting Multidimensional Arrays Into A Spill Range

user_11732320 0
Viewed 331

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:

enter image description here

Desired Outcome:

enter image description here

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

=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).

=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.

spill-range multidimensional-array excel-formula excel
1 Answers
=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:

Stackoverflow

PS partial credits to Mark Fitzgerald for posting this answer

user_126342300
Related