Excel :Data Preparation

[harsha384]

Hi All,

I need help with the below mentioned question :

Input :

				Curreny Year Profit				CURRENY YEAR SALE
Product	JAN	FEB	MARCH	APRIL	MAY	JUNE	JAN	FEB	MARCH	APRIL	MAY	JUNE
A	1	1	1	1	1	1	1	1	1	1	1	1
B	2	2	2	2	2	2	2	2	2	2	2	2
C	3	3	3	3	3	3	3	3	3	3	3	3
D	4	4	4	4	4	4	4	4	4	4	4	4
E	5	5	5	5	5	5	5	5	5	5	5	5
F	6	6	6	6	6	6	6	6	6	6	6	6

Expected Output :

Product	KPI	Month	Values
A	Curreny Year Profit	JAN	1
B	Curreny Year Profit	JAN	2
C	Curreny Year Profit	JAN	3
D	Curreny Year Profit	JAN	4
E	Curreny Year Profit	JAN	5
F	Curreny Year Profit	JAN	6
A	Curreny Year Profit	FEB	1
B	Curreny Year Profit	FEB	2
C	Curreny Year Profit	FEB	3
D	Curreny Year Profit	FEB	4
E	Curreny Year Profit	FEB	5
F	Curreny Year Profit	FEB	6
A	CURRENY YEAR SALE	JAN	1
B	CURRENY YEAR SALE	JAN	2
C	CURRENY YEAR SALE	JAN	3
D	CURRENY YEAR SALE	JAN	4
E	CURRENY YEAR SALE	JAN	5
F	CURRENY YEAR SALE	JAN	6
A	CURRENY YEAR SALE	FEB	1
B	CURRENY YEAR SALE	FEB	2
C	CURRENY YEAR SALE	FEB	3
D	CURRENY YEAR SALE	FEB	4
E	CURRENY YEAR SALE	FEB	5
F	CURRENY YEAR SALE	FEB	6

Please help!

Thanks and Regards

Harsh

[Joe_Mako · Accepted solution]

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 solution]

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 solution]

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.

[Federica_FF]

That's amazing!

In the very begin I used to create a formula that writes the number 1 in each row plus a Running total grouped by month (in this specific case) to create the sequence of numbers, but since I discovered the group option in the Multi-row tool, the Multi-row tool really became my best friend!

It can be used like an advanced RecordID that starts again from 1 when the month changes (or in general based on some grouping logic), like the index() table calculation in Tableau partitioned by some dimension in your table.

Very powerful!

[harsha384]

Thanks Joe_Mako for the solution!

[harsha384]

Thanks @Federica_FF and @Joe_Mako for the solution. I really admire the group for being so active and providing solutions quickly. I have implemented the combined solution, seems to be more effective.

Thanks and regards

Harsh