Different rows into different columns

Question

I have a Excel sheet that contains hundreds of data like this:

Name 1  Name 2  Name 3  Name 4

   A      Aa      Aaa   Aaa


Name 5  Name 6  Name 7  Name 8

   B     Bb      Bbb    Bbbb

I need to put them like this, all at once:

Name 1      A
Name 2      Aa
Name 3      Aaa
Name 4      Aaa

Name 5      B
Name 6      Bb
Name 7      Bbb
Name 8      Bbbb

How can I do it? Any macro method?

Answer 1

I would resolve this with the Power Query Add-In. It takes a few steps to get there but no Macro code or changes to your input data structure are required.

I've built a prototype which you can view or download - its "Power Query demo - Unpivoting pairs of rows into two columns.xlsx" in my One Drive:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

Basically my technique was to first build an intermediate query which filters down to every odd-numbered row, and adds an "Index Row Pairs" column to keep track of the row pairs. Then I created duplicate of that Query, which filters down to the even-numbered rows. Then I used a Merge to join (on the "Index Row Pairs" column) to the first Query and get the Odd and Even row columns all in a single row. Then I added a pair of Unpivots to transform the 4 Odd row columns and the 4 even row columns into 8 rows. Then I added a calculation to filter down to the required set of rows.