QUESTION :
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?
EDIT
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.
ANSWER :
Use PowerQuery to get the result:
You can refresh directly if you change the data source Table.
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.