macro to pivot data

poojitha4

Hi everyone,

I have to create pivoted data from a excel sheet, I have columns like 

Market	Week	Competitor	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
A	Week 1	123
B	Week 2	236
A	Week 3	256
B	Week 4	456
A	Week 5	856
B	Week 6	258

So I have three dimensions which are week, Market and competitor. Columns 1-16 are different metrics. I need to pivot this data by the week. So the new column names will essentially will be Competitor_Market_1, Competitor_Market_2, Competitor_Market_3,Competitor_Market_4,....Competitor_Market_16. I have 2 different markets and 10 different competitors, and 16 different metrics, so essentially I need to have 2*10*16 columns.

I am aware that "cross tab" node is useful here, but my only concern is I have to do it for all the 16 metrics and that makes this workflow very bulky.

I want to change this into a macro and I think that is what macros are for, make bulky workflows look easy and simple. I'm not entirely sure on how. I attach a sample image of what I tried to do with the workflow.

I want the part where I use the 16 cross tabs to become a macro, any insights will be helpful.

Thank you,

Poojitha.

Capture.PNG

john_miller9

@poojitha4 I'm not sure that a macro is needed as it looks like the workflow below will accomplish what you've outlined.  If there is something I misunderstood, please let me know.  Thanks.

Macro to Pivot Data.yxzp