Transpose Excel data, on multiple columns

Question

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.

This will probably require vba as any formula approach will require array formulas and that many will bog down Excel. What have you tried? By your profile you understand SQL, Why not move this table into a database and use sql to do it? — Scott Craner
– Scott Craner, Commented Sep 9, 2016 at 13:38
My experience transposing in SQL is that it works when you have few columns (in this instance question) to transpose. Going on 30 or more columns it gets rather messy. I have been thinking about moving it into R and work on it in there. — Linkan
– Linkan, Commented Sep 9, 2016 at 14:10

Linkan · Accepted Answer · 2016-09-12 08:29:55Z

2

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.

answered Sep 12, 2016 at 8:29

Linkan

5791 gold badge7 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transpose Excel data, on multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related