How to transpose data in Excel while using the 1st column as unique identifier?
I need help transposing some 3rd-party Excel output that comes in this format below:
| Employee | Question | Response |
|---|---|---|
| Bob | Q1 | Yes |
| Bob | Q2 | No |
| Bob | Q3 | 100 |
| Jane | Q1 | No |
| Jane | Q2 | No |
| Jane | Q3 | 50 |
| Tom | Q1 | No |
| Tom | Q2 | Yes |
| Tom | Q3 | 0 |
Background: This is survey data containing up to 10 questions and each employee MUST answer each question. So if data was collected from 10 employees for a survey of 3 questions, then the output file will contain (10x3) 30 rows of data
I need to rearrange this data for the "business side" and I realized that the desired output is beyond the scope of simply using TRANSPOSE() in Excel
Here is the final result that I've been asked to design
| Employee | Q1 | Q2 | Q3 |
|---|---|---|---|
| Bob | Yes | No | 100 |
| Jane | No | No | 50 |
| Tom | No | Yes | 0 |
Basically, I need 1-row per employee with each question horizontally lined up and their responses.
Is this even possible? If so, any help would be greatly appreciated!
cheers