Forum Discussion
Stacking multiple rows into separate columns with expanding table
Eng_Noah Perhaps something like this:
=LET( header, HSTACK("Stacked "&{"Dates","QTY","Package"}, "Item Name"), dates, TOCOL(IF(SEQUENCE(, 3), IFS(A2:A1000, A2:A1000)), 2), rws, ROWS(dates), data, TAKE(WRAPROWS(TOCOL(B2:G1000), 2), rws), items, TOCOL(IF(SEQUENCE(rws/3), SUBSTITUTE(CHOOSECOLS(B1:G1, 1, 3, 5), "QTY ", ""))), VSTACK(header, HSTACK(dates, data, items)) )
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
Thank you very much! This worked. I did fix what I presume is a typo in the following part of the formula:
REDUCE({"Stacked Date"."Stacked Qty"."Stacked Packages"},SEQUENCE(ROWS(rng)),
Used commas instead of periods to separate the column headers. Other than that, adjusted everything to fix my actual data format and it worked perfectly.
I did fail to mention something in my original question though and I apologize for it. Basically, append the item's name in the same row as the QTY and Packages. In my original form, I would just extract the column's header and use that as the data that would go in the row.
The expected output would look as follows:
Again, apologies for forgetting this detail.
Eng_Noah Perhaps something like this:
=LET( header, HSTACK("Stacked "&{"Dates","QTY","Package"}, "Item Name"), dates, TOCOL(IF(SEQUENCE(, 3), IFS(A2:A1000, A2:A1000)), 2), rws, ROWS(dates), data, TAKE(WRAPROWS(TOCOL(B2:G1000), 2), rws), items, TOCOL(IF(SEQUENCE(rws/3), SUBSTITUTE(CHOOSECOLS(B1:G1, 1, 3, 5), "QTY ", ""))), VSTACK(header, HSTACK(dates, data, items)) )- Thank you SO much for this!
Modified it to work with my actual data and it's perfect.
