Stacking multiple rows into separate columns with expanding table

[Eng_Noah]

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!

[#2]

@Eng_Noah 

=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.

[#3]

@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!

[#4]

@djclements 

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?

[#5]

@Eng_Noah 

=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.

[#6]

@OliverScheurich 

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.

[#7 · Accepted solution]

@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))
)

[#8]

@Eng_Noah 

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)
)

[#9]

https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-automatically-create-a-new-row-with-da...


//select * from unstack_basic limit 20;
cli_unstack~unstack_basic~QTY,Packages;
select colIndex[0:2] from unstack_basic_unstack;

[#10]

@peiyezhu 

//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

[#11]

i need to open it because i really need it for bussines

[#12]

Thank you SO much for this!

Modified it to work with my actual data and it's perfect.