Move data in column into row

[MarlonvanDijk]

Hello,

I have a large excel-datafile with data stored in columns, but want to get all into one row (this format is needed for further analysis).The dataset is too large to do this manually. Below an example:

Original
Author - Obs.- Obs.- Obs.
X aaa bbb ccc
Y ddd eee

Output
Author - Obs.
X aaa
(X) bbb
(X) ccc
Y ddd
(Y) eee

Preferably I have the author-cell copied in all new columns, but this is not completely necessary. Also note that each column doesn't have the same amount of rows in them.

Is this possible to do in excel? Can it be done by writing a macro? Thanks in advance for your help!

 

[MarlonvanDijk]

I see the formatting of the example changed. The bold "author" and "Obs" are labels of the rows, with corresponding values in the lines under it