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

by

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

More Related Contents:

  1. Excel – Extract substring(s) from string using FILTERXML
  2. Can Excel’s INDEX function return array?
  3. Excel-VBA – Insert new first column in datafield array without loops or API calls
  4. Creating an Array from a Range in VBA
  5. Expanding column cells for each column cell
  6. How do I slice an array in Excel VBA?
  7. Check if a value is in an array or not with Excel VBA
  8. Matching values in string array
  9. Deleting Elements in an Array if Element is a Certain value VBA
  10. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  11. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  12. Return Index of an Element in an Array Excel VBA
  13. Writing an array to a range. Only getting first value of array
  14. Excel VBA: Range to String Array in 1 step
  15. Faster way of using Sumifs()
  16. How to initialize a multidimensional array variable in vba for excel
  17. VBA using ubound on a multidimensional array
  18. Turn Excel range into VBA string
  19. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  20. Why am I having issues assigning a Range to an Array of Variants
  21. Subscript out of range error with an array – no idea why?
  22. VBA Excel large data manipulation taking forever
  23. Performance difference between looping range vs looping array
  24. Create Chart from Array data and not range
  25. Excel VBA Quickest way to sort an array of numbers in descending order?
  26. Simple VBA array join not working
  27. Excel VBA – How to Redim a 2D array?
  28. ReDim Preserve “Subscript Out of Range”
  29. Please help me access nested array
  30. Array of Matrices in MATLAB

Leave a Comment