I have a table in wide format, here’s a simplified version:
I’ve restructured it to long format like this (using Tableau add in):
The thing is, I’ve had to start over with the restructure every time the table is updated.
My question is if there is a way to create the long format table using formulas so it’s dynamic and will update with the data?
I can’t use power-query as suggested in some answers because this is part of a project that’s going to sit on a few different computers where I cannot rely on Power-Query being installed/activated. I already created a VBA program to do this before I found out I couldn’t use macros in some of the computers where I’ll be sending this. Therefore I really need to do this with good old built-in excel functions only.
The actual data has two more category variables and about 40 more data columns, but the structure is constant, only the data within will be updated.
Hopefully you found an answer but for anyone else googling this there are some formulas halfway down this page: https://trumpexcel.com/source-data-for-pivot-table/ that can be used instead of Power Query.