Forum Discussion
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 image below). I am trying to get them to be summarized in three separate columns: the date, QTY, and Packages (right side of reference).
I already know I have to use VSTACK so I can put the code in the header column rather than in the data rows. However, I'm not familiar enough with Excel's functions to replicate what I was already doing with Google Sheets.
Any and all help is greatly appreciated!
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)) )
=LET(rng,A2:G6,HSTACK(TOCOL(IFNA(EXPAND(TAKE(rng,,1),,3),TAKE(rng,,1))),WRAPROWS(TOROW(DROP(rng,,1)),2)))
This could be an alternative with HSTACK.
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.
This will do it with 'dates' and 'data' being dynamic items:
=LET( ext_dates, TOCOL(dates * {1, 1, 1}), wrapped, WRAPROWS(TOCOL(data), 2), HSTACK(ext_dates, wrapped) )- https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-automatically-create-a-new-row-with-data/3e78505c-0779-4d20-9387-0323a7df2b80
//select * from unstack_basic limit 20;
cli_unstack~unstack_basic~QTY,Packages;
select colIndex[0:2] from unstack_basic_unstack;//select * from unstack_basic limit 20;
cli_unstack~unstack_basic~QTY,Packages;
cli_no_header;
select * from unstack_basic_unstack;
1-Jun-23 90 15 Item 2 1-Jun-23 100 10 Item 1 1-Jun-23 250 5 Item 3 14-Oct-23 60 10 Item 2 14-Oct-23 170 17 Item 1 14-Oct-23 500 10 Item 3 15-Oct-23 80 8 Item 1 15-Oct-23 210 35 Item 2 15-Oct-23 600 12 Item 3 5-Jun-23 150 25 Item 2 5-Jun-23 200 20 Item 1 5-Jun-23 350 7 Item 3 9-Aug-23 100 2 Item 3 9-Aug-23 150 15 Item 1
- i need to open it because i really need it for bussines
