CODEWITHSUNDEEP
excel
Linkan
Linkan

Reputation: 579

Transpose Excel data, on multiple columns

I have a dataset in Excel that I need to transpose. It is survey data and the first column is the month of the survey. The second is unique to each company, the third is a sector code for that company (which can change over time), the forth is a Size variable and then there is question number and answer columns. I want to be able to do Pivot tables of this, but as I understand it I need to get each question in its own column to be able to cross tabulate in the pivot table. Eg what has companies answered on question 2, dependent on their answer on question 1. Ho wan I transpose the data?

From this

Period   Company  Sector  Size   Question Answer
201601   101      Cons    Small  1        2
201601   101      Cons    Small  2        1
201601   101      Cons    Small  3        2
201601   102      Int     Small  1        3
201601   102      Int     Small  2        1
201601   102      Int     Small  3        1
201602   101      Cons    Small  1        3
201602   101      Cons    Small  2        2
201602   101      Cons    Small  3        1
201602   102      Int     Small  1        3
201602   102      Int     Small  2        1
201602   102      Int     Small  3        2

To this

Period  Company  Sector   Size  Question1 Question2 Question3
201601  101      Cons     Small 2         1         2
201601  102      Int      Small 3         1         1
201602  101      Cons     Small 3         2         1
201602  102      Int      Small 3         1         2

There can be up to about 30 questions in one file, about 1500-2000 companies and in my first files I will have 4 months. The companies are grouped on at most 5 sectors and two different sizes.

Upvotes: 1

Views: 376

Answers (1)

Linkan
Linkan

Reputation: 579

Thanks to a comment from Doug Glancy I could figure out how to do things.

Create a Pivot Table with all columns in Row Lables except for Question and Answer. Then put Question in Column Labels and Answer in Values. Choose to sum the values.

To get the format correct, in the Pivot Table Tools - Design menu, choose Subtotals - Do not show Subtotals. Copy the resulting table into a new workbook without the sums column and row.

Upvotes: 2

Related Questions