Jtdylktuy

I have an Excel 365 (2016) file that contains rows of questions/answers that I need converted to columns as shown below.

Person  Question    Answer      

Bob     Dogs Name   Rover       

Bob     Cats Name   Mewl        

Bob     Car Type    Mustang     

Bob     Own/Rent    Own     

Carol   Cats Name   Tiger       

Carol   Car Type    Ford        

Carol   Own/Rent    Rent        

Ted Own/Rent    Own     

Alice   Dogs Name   Growler     

Alice   Cats Name   Cali        

Alice   Car Type    GMC     

Alice   Own/Rent    Own     





       Dogs Name    Cats Name   Car Type    Own/Rent

Bob    Rover        Mewl        Mustang     Own

Carol               Tiger       Ford        Rent

Ted                                         Own

Alice   Growler     Cali        GMC         Own

Try to use Power Query get the result:

1. Select range- Go to Data- From Range/Table:
   




2. Select Question Column- go to Transform tab- Select Pivot Column- Values Column select Answer- Advanced options select Don't Aggreate:
   




3. Close and Load:
   

in order to get your column and row headers, you need to generate a list of unique entries. This can be achieved formulaicly, you can manually type it out or you can use a built in tool in the ribbon called text to columns:

Step 1

Copy the range you want unique values from to a separate area. Then select the copied list:

Step 2

Select Remove Duplicates from ribbon

Step 3

Click the "My data has headers" in the upper right if applicable to your range, and then select ok

Step 4

Repeat Steps 1 to 3 for the other column

Step 5

Transpose the unique column that you want to use for column headers. Select the column you want to transpose. right click and select copy:

Select the location where the first cell of the header row is to be. In this example H1. Right click and under the paste choices, select transpose:

Step 7

Fill in the table. Base on the examples that I have been showing, in H2 enter the following formula and copy it down and right to fill in the table:

=IFERROR(INDEX($C:$C,AGGREGATE(14,6,ROW($A$2:$A$13)/(($A$2:$A$13=$G2)*($B$2:$B$13=H$1)),1)),"")

AGGREGATE with formula choice 14 performs array operations within its brackets. As a result do not use full column references within these brackets. NOTE the $C:$C is outside the AGGRGRATE function where it is safe to use full column references.

There are other ways to achieve this.

What you are looking for is called a Pivot Table. This function can take repeated data from one or more columns and pivot it to become a header.

All modern spreadsheet programs use that name for this function, but each one handles it slightly differently. Microsoft provides an introduction to this function in your specific application:
https://support.office.com/en-gb/article/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576