Forum Discussion
Noob needs help with shaping data
Hi,
I have solved a similar problem at this link - Rearrange a multi heading dataset into a single heading one which is Pivot ready.
Hope this helps.
Awesome! Thank you so much!
Super UserYou are welcome.
Hi Ashish! Sorry to trouble you.
I am not clever enough to apply your example to my problem. There is an additional header row that I cannot figure out to transform. Can you take a look at the sample data file attached and provide a step by step? I will be most grateful!
Note: Category 2 starts from column BN.
Regards,
Yee Pin
- Ashish_Mathur
Hi,
My method will not work on your dataset because there are 3 headings for every column - in rows 1,2 and 3. The only way my method can be made to work on your data is if we combine the headings in rows 1 and 2. This way we will be left with only 2 headings per column. In the final output though, we will again break up the combined strings and show them in seperate columns. If you are agreable to this minor change in your inout data, then i can solve this for you. Also, what is the use of the columns from FV:GM? Can they be deleted?
Hi Ashish! Thank you so much for responding!
Yes, let's try the approach that you have selected.
I have checked and Columns FV:GM are actually the comments for the Category. So in the data set, there are Safe and Unsafe comments for:
- General
- Category e.g. reactions of people, positions of people, etc
- Subcategory e.g. reactions of people - Adjusting PPE, reactions of people - rearranging job, positions of people - falling, positions of people - strking against or being struck by objects, etc.
Can we keep them?