Database creation and cleanup for large dataset

Hi everyone,

I am working on a research project for which I need to create a database for 2000+companies. I got the list of the companies online whose current format looks similar to the one "currentlist" picture attached herecurrentlist.PNG. For my project, I need to list the companies in the manner shown on "expectedlist" picture. I am looking at 10 years of monthly data for these companies, and will be looking at their revenues and all. I do not know how can I structure my data in a way that for each company I have 1984 Month 1, 1984 Month 2 and so on. I would highly highly appreciate any help from you all.

Hi Olly,
I can get the data for these companies without once I get the dates in place. I will be using Bloomberg tab in Excel to extract the data. I need help to automatically create the second column in my second picture for each company. I do not know how to even start that without manually doing all the entries.

Hi Olly,

Thnk you so much for your help. Yes, you have the right idea for what I want to do with my data. Your code gave me the result in the picture, but I need to arrange my data per year per month for a company.
Example:
Company 1 1984M1
1984M2
...
1984M12
Company 2 1984M1
1984M2
...
1984M12
Company 3 1984M1
1984M2
...
1984M12
I tried using Power Query, and wrote down the code you have posted.
And for some reason I got null in the columns where I was supposed to have company names. Thank you once again!! Attachment 631284

Hi Olly,
Yes, it is finally working well with my actual data. Thank you very very much!