Re-tabulate Data in different layout

[afzalbaharudin]

Hello wonderful community!

I can't figure out how to re-tabulate the table from the left side [Table1] to follow the format/layout like the one on the right side [Table2]. Table1 is a purely input table and records all information in a monthly basis. I'm stuck and I don't know what formula or functions to start with.

Could anyone please help me on this?

Regards,

Afzal

.

[#2 · Accepted solution]

@afzalbaharudin I would use PowerQuery (PQ) for this, assuming that you are working with a substantial amount of data, over and over again. Example attached. If you are not familiar with PQ, the site in the link below would be a good place to start learning about it.

https://exceloffthegrid.com/power-query-introduction/ 

Chapter 13 deals with "Unpivot data", which is what you want to do.

If, on the other hand, your data set isn't all that large, you could consider to just do a few manual copy/paste steps and forget about complex formulae. That would probably take 30 seconds.