Forum Discussion

Melvin_Khoo's avatar
Melvin_Khoo
Copper Contributor
Dec 03, 2022

Transforming data using new array shaping formula in excel

  Hello Community,   I'm transforming a set of data into tabular form with the new array shaping formula such as vstack, tocol, chooserow and so forth   This is how my data set looks like Te...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 03, 2022

Melvin_Khoo 

sample illustrates the logic if number of rows in the table is the same as number of date columns. To take it literally the could be like

=DROP(
    REDUCE(
        "",
        SEQUENCE(ROWS(Tabelle1)),
        LAMBDA(a,v,
            VSTACK(
                a,
                HSTACK(
                    Tabelle1[Text 1],
                    Tabelle1[Text 2],
                    TRANSPOSE(Tabelle1[[#Headers],[Date 1]:[Date 5]]),
                    TRANSPOSE(DROP(CHOOSEROWS(Tabelle1, v), , 2))
                )
            )
        )
    ),
    1
)

But what shall be the logic of the final table if, for example, you have same 5 rows in table but only two date columns?

 

Resources