Excel :Data Preparation

Question

Hi All,

I need help with the below mentioned question :

Input :

Curreny Year Profit   CURRENY YEAR SALE    ProductJANFEBMARCHAPRILMAYJUNEJANFEBMARCHAPRILMAYJUNEA                                1           1              1                          1              1             1              1                                1             1           1              1             1B                                2           2              2                          2              2             2              2                                2             2           2              2             2C                                3           3              3                          3              3             3              3                                3             3           3              3             3D                                4           4              4                          4              4             4              4                                4             4           4              4             4E                                5           5              5                          5              5             5              5                                5             5           5              5             5F                                6           6              6                          6              6             6              6                                6             6           6              6             6

Expected Output :

ProductKPIMonthValuesACurreny Year ProfitJAN              1BCurreny Year ProfitJAN              2CCurreny Year ProfitJAN              3DCurreny Year ProfitJAN              4ECurreny Year ProfitJAN              5FCurreny Year ProfitJAN              6ACurreny Year ProfitFEB              1BCurreny Year ProfitFEB              2CCurreny Year ProfitFEB              3DCurreny Year ProfitFEB              4ECurreny Year ProfitFEB              5FCurreny Year ProfitFEB              6ACURRENY YEAR SALEJAN              1BCURRENY YEAR SALEJAN              2CCURRENY YEAR SALEJAN              3DCURRENY YEAR SALEJAN              4ECURRENY YEAR SALEJAN              5FCURRENY YEAR SALEJAN              6ACURRENY YEAR SALEFEB              1BCURRENY YEAR SALEFEB              2CCURRENY YEAR SALEFEB              3DCURRENY YEAR SALEFEB              4ECURRENY YEAR SALEFEB              5FCURRENY YEAR SALEFEB              6

Please help!

Thanks and Regards

Harsh

Joe_Mako · Accepted Answer

How about the attached?

It is fairly dynamic, as can handle any number of KPIs and months, as long as each KPI has the same number of month columns. If there are a different number of columns per KPI, the workflow would need to be adjusted.

Federica_FF · Accepted Answer

Hi!

In attachment another way to get same result (love alteryx for that!), made only with transpose, crosstab and union by name tools that should be dynamic and work no matter the number of months in the table.

Joe_Mako · Accepted Answer

Nice work @Federica_FF !

I incorporated your Multi-Row Formula step technique into my workflow, bringing it down to 14 steps, I like how you used the used the Group option.