Empresas
Empregos
  • Sobre nós
  • Soluções
    • Publicação de vagas
      Publique sua vaga e receba candidatos qualificados em 48h.
    • Avaliações de candidatos
      Mais de 500 testes técnicos e psicológicos, mais anti-fraude.
    • Headhunting
      Busca executiva personalizada do início ao fim.
    • Folha de Pagamento + EOR
      Dispersão de folha e EOR em mais de 15 países da LATAM.
  • Preços
  • Empregos

0

465
Visualizações
How Do I Make FLATTEN Work for Non-Contiguous Ranges?

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)

    )   
)
over 4 years ago · Santiago Trujillo
3 Respostas
Responde à pergunta

0

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<>""))
over 4 years ago · Santiago Trujillo Relatório

0

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

over 4 years ago · Santiago Trujillo Relatório

0

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.

over 4 years ago · Santiago Trujillo Relatório
Responde à pergunta
Encontrar trabalhos remotos

Descubra a nova forma de encontrar um emprego!

melhores empregos
Principais categorias de trabalho
Empresas
Postar vaga Preços Comercial
Jurídico
Termos e Condições Política de privacidade
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Recomende algumas ofertas para mim
Preciso de ajuda