3

I have a FLATTEN LAMBDA function that flattens data in an array. This works well, but I want to integrate another array argument so I can use non-contiguous ranges.

In my example, the range A1:B6 is housed in array and returns the flattened data.

How can I include an array2 argument that accepts D1:D6 as an additional range?

Example

Formula:

FLATTEN =

LAMBDA(array,

LET(
    rows,ROWS(array),
    columns,COLUMNS(array),
    sequence,SEQUENCE(rows*columns),
    quotient,QUOTIENT(sequence-1,columns)+1,
    mod,MOD(sequence-1,columns)+1,

    INDEX(IF(array="","",array),quotient,mod)

    )   
)
Statto
  • 410
  • 3
  • 9

3 Answers3

4

Edit 7/4/22:

ms365 now has introduced a function called VSTACK() and TOCOL() which allows for the the functionality that we were missing from GS's FLATTEN() (and works even smoother)

In your case the formula could become:

=TOCOL(A1:D6,1)

And that small formula (where the 2nd parameter tells the function to ignore empty cells) would replace everything else from below here. If C1:C6 would hold values you don't want to incorporate you can try things like:

=VSTACK(TOCOL(A1:B6),D1:D6)

Previous Answer:

You can't really create a LAMBDA() with an unknown number (beforehand) of arrays to include in flatten. The fact that you have arrays of multiple columns will contribute to the "trickyness". One way to 'flatten' multiple columns in this specific way would be:

enter image description here

Formula in G1:

=LET(X,CHOOSE({1,2,3},A1:A6,B1:B6,D1:D6),Y,COLUMNS(X),Z,SEQUENCE(COUNTA(X)),INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1))

EDIT: As per your comment, you can extend this as such:

=LET(X,CHOOSE({1,2,3},IF(A1:A6="","",A1:A6),IF(B1:B6="","",B1:B6),IF(D1:D6="","",D1:D6)),Y,COLUMNS(X),Z,SEQUENCE(ROWS(X)*Y),FLAT,INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1),FILTER(FLAT,FLAT<>""))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • That works, except when I delete a value from one of the arrays, the blank appears as a zero. – Statto Feb 14 '22 at 14:43
  • 1
    Replacing `INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1)` with `list, INDEX(X,CEILING(Z/Y,1),MOD(Z-1,Y)+1), IF(LEN(list) = 0,"", list)` should work – Tragamor Feb 14 '22 at 14:47
  • @Tragamor, sorry I had to rewrite my comment. The trickyness here is that `CHOOSE()` will change empty in a zero. Therefor we need to handle the empty cells at the source. See the edit. – JvdV Feb 14 '22 at 14:55
  • What about if you use choose as column indices to slice A1:D6 instead? the code may be more concise as I can see this bloating if you have many more columns – Tragamor Feb 14 '22 at 15:00
  • 1
    @Tragamor, you are spot on. There is no 'FLATTEN()' and trying to recreate this is tricky. Your suggestion though is *only* going to work if OP actually has his data in those cells. I read the question as though the 2nd, 3rd, 4th etc array does not necessarily have to reside on the same tab in the same rows. – JvdV Feb 14 '22 at 15:02
  • The other thing I've noticed is the behaviour when the arrays have varying row counts. Google Sheets' FLATTEN function is a bit more flexible in that respect. – Statto Feb 14 '22 at 15:03
  • It may be better to have a second function which is a 'list append' function? You could generate 1D lists of contiguous ranges from the Lambda function then append 1D lists together (havent' seen code for that though) – Tragamor Feb 14 '22 at 15:06
  • @Tragamor, another good suggestion, however that would not work nomore if you need to list items from left to right first accounting for each array (see the results above). GS has this issue down way better. – JvdV Feb 14 '22 at 15:07
2

It's a cheat, but:

FLATTEN =

LAMBDA(array,

LET(
    rows,ROWS(array),
    columns,COLUMNS(array),
    sequence,SEQUENCE(rows*columns),
    quotient,QUOTIENT(sequence-1,columns)+1,
    mod,MOD(sequence-1,columns)+1,
    unpiv, INDEX(array,quotient,mod),
    FILTER(unpiv, unpiv<>"")

    )   
)

Where your array has been extended to A1:D6 as the input.

I think JvdV's answer will be the best depending on the input format you want, but I had already written this out, so here goes...

You could do:

=LET( array1, A1:B6, array2, D1:D6,

      rows1,ROWS(array1),     rows2,ROWS(array2),
      columns1,COLUMNS(array1),   columns2,COLUMNS(array2),
      rows, MIN(rows1, rows2),
      columns, columns1 + columns2,
      sequence,SEQUENCE(rows*columns),
      quotient,QUOTIENT(sequence-1,columns)+1,
      mod,MOD(sequence-1,columns)+1,

      IFERROR(INDEX( IF( ISBLANK(array1),"",array1),quotient,mod),
              INDEX(IF( ISBLANK(array2),"",array2),quotient,MOD(sequence-1,columns2)+1) )

)

It will take multi-column/row inputs to both arrays.

enter image description here

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • Thanks for your efforts, but that is a bit of a stopgap solution really. I need to find a way of having an additional `array` argument. – Statto Feb 14 '22 at 14:30
  • @Statto - when there is a blank in either of the arrays, do you want it to appear as "" or get skipped? – mark fitzpatrick Feb 14 '22 at 15:04
  • I want it to appear as "". – Statto Feb 14 '22 at 15:06
  • @Statto - ok, this does that. I really like the solution of JvdV as it is so parsimonious in it's processing, but the inputs have to be made column-by-column. For a LAMBDA function, it might be better to build a column-wise append and call your FLATTEN with the APPENDC as the input. e.g., `FLATTEN(APPENDC(A1:A6,D1:D6))` – mark fitzpatrick Feb 14 '22 at 15:24
  • @Statto - Last comment was unclear: ok, this does that. I really like the solution of JvdV as it is so parsimonious in its processing, but the inputs have to be made column-by-column. For a LAMBDA function, that's tedious. Perhaps it would be better to keep your current FLATTEN as is and build a column-wise append LAMBDA function (let's call it APPENDC) and call your FLATTEN with the APPENDC as the input. e.g., `FLATTEN(APPENDC(A1:A6,D1:D6))` – mark fitzpatrick Feb 14 '22 at 16:10
2

Starting from the article here and updating based upon observations about empty values in the arrays and allowing varying sized arrays we can get two formulae which you should be able to translate to Named LAMBDA functions for 'stacking' and 'shelving' arrays.

Stack Arrays

=LET(rngA, A1:C5, rngB, A9:D11,
rowsA, ROWS(rngA), rowsB, ROWS(rngB),
NumCols, MAX(COLUMNS(rngA), COLUMNS(rngB)),
SeqRow, SEQUENCE(rowsA + rowsB), SeqCol, SEQUENCE(1, NumCols),
Result, IF(SeqRow <= rowsA, INDEX(IF(rngA="","",rngA), SeqRow, SeqCol), 
    INDEX(IF(rngB="","",rngB), SeqRow-rowsA, SeqCol)),
arr, IFERROR(Result,""), arr)

Shelve Arrays

=LET(rngA, A1:C5, rngB, B8:D12,
colsA, COLUMNS(rngA), colsB, COLUMNS(rngB),
NumRows, MAX(ROWS(rngA), ROWS(rngB)),
SeqRow, SEQUENCE(NumRows), SeqCol, SEQUENCE(1, colsA + colsB),
Result, IF(SeqCol <= colsA, INDEX(IF(rngA="","",rngA), SeqRow, SeqCol),
    INDEX(IF(rngB="","",rngB), SeqRow, SeqCol-colsA ) ),
arr, IFERROR(Result,""), arr)

Once you have a contiguous array, you can apply the formula you already have:

Updated to use a spill range for ease of testing...

=LET(data, A1#,
rows, ROWS(data), cols, COLUMNS(data),
seq, SEQUENCE(rows*cols,,0),
list, INDEX(IF(data="", "", data), QUOTIENT(seq, cols)+1, MOD(seq, cols)+1),
FILTER(list, LEN(list)>0))

This approach is really geared towards the named LAMBDA functions because otherwise you will end up with monstrous formulae and the other approaches may well be better in that case.

Tragamor
  • 3,594
  • 3
  • 15
  • 32