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.

[cmcclellan]

Can you supply some sample data in XLS or CSV format ?

I looked at the image and first thought that you wanted to transpose, but reading your post I don't quite understand why you want to do the cross-tab and merge.

Anyway, I'd be tempted to try with a single cross-tab tool and maybe some filtering after that.

Why do you want to do this though ?   I think I'd be wanting to transpose the 1-16 columns though.

[poojitha4]

Hi @cmcclellan,

I have attached a sample xslx book which has sample input and output sheets. I have this requirement(client requirement) which I had to meet and I did, I just want to push through and find an optimal solution. Any insights?

Thank you,

Poojitha

[john_miller9 · Accepted solution]

@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.

[poojitha4]

@john_miller9, you are absolutely right my friend! That's is all I need. Silly me. Thank you so much.