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.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1214422-cut-copy-and-insert-on-a-loop

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.