Forum Discussion
Transpose issue (maybe)
Hello!
I am trying to put this data
Into the format of the attached file.
Essentially I need to change Coloumn "Plan_Name" into unique headers as the template asks, but I do not know how to move the adjacent cells of the plan name as the template asks without having to copy and paste each individual cell. This is just a sample screenshot, in total I have 1500 lines that I need to move to match the template.
I would appreciate any help or suggestions.
7 Replies
There are different columns in screenshot and Excel file. Do you mean group records in screenshot by Plan Name and add it's name on the top of each group as in file?
SergeiBaklan I appreciate your response! Yes essentially that is what I am trying to accomplish. But I don't know how the rest of the data (DOB, SSN, name, cost) will be moved to match the plan name.
To confirm, you essentially want to do this?
If so, your data can be loaded into Power Query and re-shaped quickly. Power Query refers to it as Pivoting the column. In this case, the Plan column is pivoted with Coverage as the values.
- Are you trying to re-order or move columns in your Excel worksheet? If so, highlight the whole column, move your cursor to the edge of the selected range (it should change from a cross to a four direction pointer), press and hold the shift key and drag the column to its new position.
- PCcoord
Ok, I think I see now... what I would do is (working with a copy of your data rather than original) is sort the columns into the same order as the template (you could use the method I mentioned earlier and record it in a macro if the data will always be in the same order). Add a filter to the data and, using the Plan field, copy / paste into the template for each. Again, you can automate all of this with VBA but not with the macro recorder because the ranges would need to be dynamic.
Another way to quickly separate data is by adding a pivot table with Plan In the Rows area and maybe life volume in Value area - this will summarise by Plan. Then by drilling down (double clicking) on the value total for each plan, you get a separate sheet of data for each. If you do the sorting I mentioned before hand you should be able to copy / paste directly into a template.
