2

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.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • 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 Aug 12 '21 at 19:21
  • Thanks for trying. – pgSystemTester Aug 12 '21 at 22:57
  • Yes, LAMBDA is the answer. And [`BYROW`](https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb) (Insiders only atm, and also gears of Lambda) – chris neilsen Aug 14 '21 at 21:27

1 Answers1

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:

Stackoverflow

PS partial credits to Mark Fitzgerald for posting this answer

P.b
  • 8,293
  • 2
  • 10
  • 25
  • 1
    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. – pgSystemTester Aug 14 '21 at 23:08
  • Not for readibility, but for the effort: +:) – T.M. Aug 15 '21 at 15:59
  • @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 ) – pgSystemTester Aug 15 '21 at 18:59