Paneldata Data Transpose

Hello guys,

I have a problem with my linear panel data set. As shown in Figure 1, I have 12 different metrics that I want to examine in a panel data analysis with 108 companies over the course of 12 years (2020-2010).

In order to import this data into Stata, I need to get all the metrics into one long data set. In Figure 2 you can see that I have to transpose the data from the other worksheets (as in Figure 1). I have also found out this function easily. In order not to have to repeat this process 1200 times for all companies, I am now asking which formula can be used to pull down this data (per metric), e.g. Market Cap, so that it also works properly.

Because if I simply pull down the transposed data, 12 lines in the original set (Figure 1) are always skipped.

Can anyone help me here?

Figure 1Figure 2