Data formatting to proper form, selected variables from rows to columns automatically

[Jacob Petersen]

Dear experts.

I have a data extract from Reuters Datastream which I need to import into Stata (Statistical software program).
However, the data format is not properly formatted, and I hope you can help me.

Please see the file attached.
'Raw data' shows the data extract from Datastream and 'Proper format example' shows an example of the proper format.

Since I will have to convert a large amount of data, it would have to be automatic in some way through formulas/VBA/pivot tables, or whatever makes most sense.

Thank you in advance, and I hope to hear from you.

Best regards,
Jacob

 

[Marc L]

Hi !

Source extract file is an Excel workbook or a text file ?
​

 

[Jacob Petersen]

Hi Marc,
Thank you for getting back to me.
The source file is an Excel workbook as in the 'raw data' sheet.

 

[Shteven]

See attached. I ran a pivot off your data and then put formulas against the pivot. Not the most elegant solution but should get the job done. Just drag the rows of formulas down depending on how large your dataset is.

 

[Jacob Petersen]

Shteven, you are a creative thinker and a genius. You just saved me so much time. The method is perfect and easy.
Thank you so much!!!!

 

[David Evans]

You can also use Power Query to achieve the same result. Depending on how you source your original data, this could become a one-step process, if I'm correctly diving your problem ...

 

[Jacob Petersen]

Thanks David, i will definitely try working with that as well.