Forum Discussion
Eng_Noah
Feb 26, 2024Copper Contributor
Stacking multiple rows into separate columns with expanding table
Hello everyone. I have a table that will continuously fill with new information. It keeps track of multiple products that we produce individually and also packaged/boxed (left side of reference i...
- Feb 26, 2024
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)) )
OliverScheurich
Feb 26, 2024Gold Contributor
=LET(rng,A2:G6,
REDUCE({"Stacked Date"."Stacked Qty"."Stacked Packages"},SEQUENCE(ROWS(rng)),
LAMBDA(u,v,
VSTACK(u,
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
WRAPROWS(TOROW(INDEX(rng,v,SEQUENCE(1,6,2,1))),2)
)))))
An alternative with VSTACK could be this formula.
=LET(rng,Tabelle8,
REDUCE({"Stacked Date"."Stacked Qty"."Stacked Packages"},SEQUENCE(ROWS(rng)),
LAMBDA(u,v,
VSTACK(u,
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
HSTACK(EXPAND(INDEX(rng,v,1),3,,INDEX(rng,v,1)),
WRAPROWS(TOROW(INDEX(rng,v,SEQUENCE(1,6,2,1))),2)
)))))
With a dynamic table the formula updates the output dynamically. The name of the table in the example is Tabelle8. This name must be replaced with the actual table name.
Eng_Noah
Feb 26, 2024Copper Contributor
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.
- djclementsFeb 26, 2024Bronze Contributor
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)) )
- Eng_NoahFeb 27, 2024Copper ContributorThank you SO much for this!
Modified it to work with my actual data and it's perfect.