Transposing grouped data (large dataset)

[Bengan1]

Hello!

I have several thousands of financial statements from different companies that I am going to analyze. My problem is that the financial statements are listed beneath each other on the same worksheet in Excel. Each company has financial information from five different financial years in columns B-F and each company’s’ financial statement is 151 rows long.

I am now trying to transpose each company’s financial statement into a format that will make it easier to analyze and calculate new variables and delete unnecessary information . I cannot simply select all information and copy and transpose, because then I will get as many columns as there are rows.

I tried simplifying how my data looks and how I am trying to structure it in the picture below. I also made a sample workbook with five companies financial statements. The sheet “Financial Statements” shows how my data will look and the sheet “Transposed data” shows how I am trying to structure it.

Is there any way to transpose data like this? I tried looking at the Power Query tool but didn’t find any solution. My sample will likely consist of about 25 000 different companies so doing it manually would take quite some time. I don’t have that much experience with macros and VBA-programming.

Help greatly appreciated, Bengan1.


https://www.excelforum.com/attachment.php?attachmentid=561932&stc=1&d=1519064365

https://www.excelforum.com/attachment.php?attachmentid=561931&stc=1&d=1519064365

[MarvinP]

Hi Bengan,

It looks like a Power Query routine would help with this problem. I saved your workbook and pulled Sheet1 into Power Query (it is called Get & Transform in 2016 Excel). Then I added a conditional column and pulled out the "Company Number" into a column of its own. Then did a Fill Down to repeat which company... Changed the Year to a date and values to currency. Then I UNPIVOTed using PQ the other columns. After pulling it back into Excel a Pivot Table seemed to be what you wanted, so I put it on your second sheet. See if this works for you. There is a lot of learning to get to my answer but I think this is what you want. You can open the attached and see the PQ steps and look at the Pivot. Hope this helps.

PQ for Bengan Answer.xlsx

[Bengan1]

Thank you for taking you time MarvinP. I will look into your process and try to implement it.