Business
Jobs
  • About Us
  • Solutions
    • Job Postings
      Post your job and receive qualified candidates in 48h.
    • Candidate Assessments
      500+ technical and psychological tests, plus anti-fraud.
    • Headhunting
      Tailor-made executive search from start to finish.
    • Payroll + EOR
      Payroll dispersal and EOR across 15+ LATAM countries.
  • Pricing
  • Jobs

0

464
Views
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 answers
Answer question

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 Report

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 Report

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 Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Sales
Legal
Terms and conditions Privacy policy
© 2026 PeakU Inc. All Rights Reserved.
Andres GPT
Show me some job opportunities
There's an error!