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.

 

mtarler's avatar
mtarler
Silver Contributor
Sep 19, 2022

mtarler OK so i looked at your solution and modified my recursive Lambda to work with it:

Stack
=LAMBDA(arr,
    LET(
        c, COLUMNS(arr),
        uniqCols, COLUMNS(UNIQUE(chooserows(arr, 1), 1)),
        copies, c / uniqCols,
        IF(
            uniqCols > 1,
            hstack(
                vstack(INDEX(arr, 1, 1), tocol(drop(arr, 1, copies - c), 3, 1)),
                stack(drop(arr, , copies))
            ),
            vstack(INDEX(arr, 1, 1), tocol(drop(arr, 1), 3, 1))
        )
    )
)

and then called by:

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

 

Resources