8

I have a table in wide format, here's a simplified version:

enter image description here

I've restructured it to long format like this (using Tableau add in):

enter image description here

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.

4
  • 4
    see the second answer:stackoverflow.com/questions/20541905/… Aug 10, 2018 at 17:22
  • can't use power-query - see my edit
    – eli-k
    Aug 19, 2018 at 11:44
  • In what way is the example simplified? For example, can there be more than 3 S columns in the first table? If the problem is a moving target, this will be outside the intended scope of a question. Is a VBA solution within your constraints?
    – fixer1234
    Aug 20, 2018 at 1:11
  • The original data has two more category variables and about 40 more data columns, but the structure is constant, only the data within will be updated. Re VBA - I already created a program to do this before I found out I couldn't use macros in some of the computers where I'll be sending this.
    – eli-k
    Aug 20, 2018 at 7:53

2 Answers 2

8

Use PowerQuery to get the result:

enter image description here

You can refresh directly if you change the data source Table.

1
  • this solution is dynamic indeed but I need to rely on built in excel functions only as this is part of a project i'm sending to a few people and I can't rely of their having PowerQuery installed/activated.
    – eli-k
    Aug 19, 2018 at 11:38
2

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .