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.
Solved! Go to Solution.
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.
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
@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.
@john_miller9, you are absolutely right my friend! That's is all I need. Silly me. Thank you so much.