Forum Discussion
Solved
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...
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)) )
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;
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 |