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)) )
Eng_Noah To repeat the dates 3 times, then stacked them vertically, use TOCOL with the IF / SEQUENCE method:
=TOCOL(IF(SEQUENCE(, 3), IFS(A2:A1000, A2:A1000)), 2)
Note: the IFS function was used here to return #N/A errors instead of zeros for any blank cells, when referencing 1000 rows to allow for growth. By setting the optional [ignore] argument of TOCOL to 2, it will ignore the errors and only return the cells that contain dates.
To quickly stack the data range into 2 columns, use TOCOL with WRAPROWS:
=WRAPROWS(TOCOL(B2:G1000, 1), 2)
In this case, the [ignore] argument was set to 1 to ignore blanks.
I hope that helps. Cheers!
I like this method, but I'm left with a question. I modified the Date one so I can use VSTACK and place the formula in the header cell.
However, for the other columns I can only get a header title for the first column (QTY one). Is there a way to make the formula automatically generate a header for the column with the packages?
- =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)),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)),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.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)) )
