Macro to copy, paste, move. Rows to Columns

[mjws]

Hi everyone,

I have a list of data which is all in one column, essentially I am trying to create a macro or formula that will move all the text from the column into the correct row. Row 2 shows how the data should look. Essentially I am trying to convert all of the data currently in column A, to the format currently in row 2. Anyone have any good ideas?

Attached is the Excel sheet snip.

 

[kennypete]

This is doable with a few lines using Power Query.
1. Make your column a Excel Table. In the example below, I renamed the Table tblBnaires and the column header (A1) "Bnaires".

2. Then from the menu, Data > From Table/Range. Go to the Advanced Editor

...and enter:

let
    Source = Excel.CurrentWorkbook(){[Name="tblBnaires"]}[Content],
    TableFromRowsListSplit = Table.FromRows(List.Split(Source[Bnaires],8)),
    Final = Table.RenameColumns(TableFromRowsListSplit,{{"Column1", "Rank"}, {"Column2", "Name"}, {"Column3", "Net Worth"}, {"Column4", "Traj"}, {"Column5", "Age"}, {"Column6", "Country"}, {"Column7", "Source"}, {"Column8", "Industry"}})
in
    Final

The code splits the list every 8th row, then takes those rows and turns them back into a table. In Final, the column headers are renamed to what you want.
"Close and Load" to wherever you want the output - I put it to C3, as shown: