Forum Discussion

Eng_Noah's avatar
Eng_Noah
Brass Contributor
Feb 26, 2024
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...
excel
  • djclements's avatar
    djclements
    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))
)

     

Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 26, 2024

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

Resources