How Do I Make FLATTEN Work for Non-Contiguous Ranges?-CodePudding

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?

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)

    )   
)

CodePudding user response：

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:

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<>""))

CodePudding user response：

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.

CodePudding user response：

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.