Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Sep 19, 2022

Patrick2788 Here is my approach.  I did create 1 lambda to perform an iterative VStack operation:

 

=LET(in,PleasePleaseMe:LetItBe!$A$1:$F$31,
headers,LetItBe!$A$1:$F$1,
hCount,COLUMNS(headers),
hCountSeq,SEQUENCE(,hCount),
allcols,HSTACK(in),
allheaders, TAKE(allcols,1),
allHcount, COLUMNS(allheaders),
colIndex, hCount*INT(SEQUENCE(,allHcount,0,1/hCount))+MATCH(allheaders,headers,0),
cSorting,SORT(VSTACK(colIndex,allcols),1,1,1),
merge,VSTACK(headers,stackN(DROP(cSorting,2),hCount)),
FILTER(merge,CHOOSECOLS(merge,1)<>0))

 

so in line 11 is this stackN Lambda function:

 

LAMBDA(arr,n,
let(c,columns(arr),
if(c>n,
   VSTACK(CHOOSECOLS(arr,sequence(,n)),stackn(CHOOSECOLS(arr,SEQUENCE(,columns(arr)-n,n+1)),n)),
   arr)
))

TBH I haven't studied your approach yet and curious to see how you solved it.

In my case I do use 2 inputs: the 3-d range and an input for the headers so you can pick which format you want, but that could be removed and automatically use the 1st page or something.

other than that I basically create an array "colindex" that give a 1,2,3... for the column headers based on the requested 'headers' order and then add an offset so it can be used to sort all the columns so all 'tabs' are in the same order.  Then my stackN Lambda function does the Vstack.

 

Patrick2788's avatar
Patrick2788
Silver Contributor
Sep 19, 2022

mtarler 

This is a fascinating approach! I had some fun testing this solution. I added some dummy rows and extended your 3D reference to:

=PleasePleaseMe:LetItBe!$A$1:$F$60000

It took a minute but Excel completed the calculation!  I tried 65K and above but no dice - crash.  Handling 720,000 rows is impressive.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 19, 2022

    Patrick2788  Here is a 3rd solution combining the above 2.  So this recursive Lambda is real slick in that it can take any 2-d array with column headers (not necessarily sequential or grouped) and stack all the columns corresponding to the requested set of Headers for the output:

     

    StackCommon 
= LAMBDA(arr, headers,
    IF( COLUMNS(headers) > 1,
        HSTACK(
            VSTACK(
                INDEX(headers, 1),
                TOCOL(FILTER(DROP(arr, 1), INDEX(arr, 1, ) = INDEX(headers, 1), ""),3,1)
            ),
            stackcommon(arr, DROP(headers, , 1))
        ),
        VSTACK(
            headers,
            TOCOL(FILTER(DROP(arr, 1), INDEX(arr, 1, ) = headers, ""), 3, 1)
        )
    )
)

     

    and calling formula is:

     

    =LET(in,PleasePleaseMe:LetItBe!$A$1:$F$31,
headers,LetItBe!$A$1:$F$1,
StackCommon(HSTACK(in),headers)
)

     

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 19, 2022
    Since the solution is using recursion I wonder if more tabs would significantly diminish the performance (i.e. 120 tabs but 6000 rows).
    I am still just learning these new text functions as you might notice I switched from CHOOSECOLS to DROP between the 2 LAMBDA functions and have to wonder if either is usable which is more or less efficient. For example:
    CHOOSECOLS(arr, SEQUENCE(,n))
    vs
    DROP(arr, n-COLUMNS(arr))

Resources