How to Cut, Copy, and Insert on a Loop
Category: [Excel] Demo Available
How to Cut, Copy, and Insert on a Loop
Requirement:
The user is attempting to prepare a large set of data for pivot table analysis. The data represents support tickets, their attributes, and their content tags. A ticket may contain anywhere from 0 to 6 tags. For tickets with multiple tags, they are represented on the spreadsheet as one ticket entry with an additional column for each tag.
The user is looking for a macro to copy the ticket number and attributes to the next row and pull the subsequent tag down. This will collect all the tags into one column for pivot table analysis but preserve the ticket number and other attributes.
The user has attached two workbook examples to try to illustrate. "Current WB" is the current state of the data. "Desired WB" is what I would like the output to be after running the macro.
Solution:
Using Power Query/Get and Transform you can do this by unpivoting the Tag columns. Here is the Mcode
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ref #", Int64.Type}, {"Trait 1", type text}, {"Trait 2", type text}, {"Trait 3", type text}, {"Trait 4", type text}, {"Trait 5", type text}, {"Tag 1", type text}, {"Tag 2", type text}, {"Tag 3", type text}, {"Tag 4", type text}, {"Tag 5", type text}, {"Tag 6", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Ref #", "Trait 1", "Trait 2", "Trait 3", "Trait 4", "Trait 5"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Tag"}})
in
#"Renamed Columns"
Obtained from the OzGrid Help Forum.
Solution provided by AlanSidman.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use a macro to copy data from multiple workbooks to one master sheet in another workbook |
| How to copy data from multiple workbooks into one |
| How to copy the entire sheet and paste as values - running on multiple tabs |
| How to select multiple worksheets and copy to desktop folder |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.