Hi there! I have exported some data out of a database, and would like to pivot it so that some column entries become column headings. This is impossible to explaine properly, so I have a file attached with what I have and what I would like to achieve. Any help would be much much appreciated!
transpose data from rows to columns grouped
- Thread starter Katya
- Start date
Sajan
Excel Ninja
Hello Katya,
Welcome to the forum!
To get text values displayed, you can use formulas to get the desired results.
To get the Pivot Table effect, we need to break up the problem into three pieces:
Display the Row Headers (in your example the Names)
Display the Column Headers (in your example the Questions)
Finally, Display the Values (in your example the Answers)
To Display the Row Headers, the following array formula can be used. Put in cell G2 and copy down:
=IFERROR(INDEX(Names,MATCH(TRUE,ISNA(MATCH(Names,$G$1:$G1,0)),0)),"---")
enter with Ctrl + Shift + Enter
To Display the Column Headers, the following array formula can be used. Put in cell H1 and copy right:
=IFERROR(INDEX(Questions,MATCH(TRUE,ISNA(MATCH(Questions,$G$1:G$1,0)),0)),"---")
enter with Ctrl + Shift + Enter
To display the values, the following array formula can be used. Put in cell H2 and copy down and to the right:
=IFERROR(INDEX(Answers,MATCH(1,(Names=$G2)*(Questions=H$1),0)),"---")
enter with Ctrl + Shift + Enter
Attached is your workbook modified to include the above formulas.
Cheers,
Sajan.
Welcome to the forum!
To get text values displayed, you can use formulas to get the desired results.
To get the Pivot Table effect, we need to break up the problem into three pieces:
Display the Row Headers (in your example the Names)
Display the Column Headers (in your example the Questions)
Finally, Display the Values (in your example the Answers)
To Display the Row Headers, the following array formula can be used. Put in cell G2 and copy down:
=IFERROR(INDEX(Names,MATCH(TRUE,ISNA(MATCH(Names,$G$1:$G1,0)),0)),"---")
enter with Ctrl + Shift + Enter
To Display the Column Headers, the following array formula can be used. Put in cell H1 and copy right:
=IFERROR(INDEX(Questions,MATCH(TRUE,ISNA(MATCH(Questions,$G$1:G$1,0)),0)),"---")
enter with Ctrl + Shift + Enter
To display the values, the following array formula can be used. Put in cell H2 and copy down and to the right:
=IFERROR(INDEX(Answers,MATCH(1,(Names=$G2)*(Questions=H$1),0)),"---")
enter with Ctrl + Shift + Enter
Attached is your workbook modified to include the above formulas.
Cheers,
Sajan.