Excel: Help Formatting Columns from Another Sheet

Question

I am trying to reformat an existing excel sheet in a new sheet.

The workbook has 2 sheets. Sheet 1 is called Responses and sheet 2 is called Question.

The format of the Responses sheet is:

|---------------------|---------------------|------------------|------------------|
|          ID         |      Question1      |     Question2    |    Question3     |
|---------------------|---------------------|------------------|------------------|
|          1          |          5          |         5        |         5        |
|---------------------|---------------------|------------------|------------------|
|          2          |          4          |         4        |         4        |
|---------------------|---------------------|------------------|------------------|
|          3          |          3          |         3        |         3        |
|---------------------|---------------------|------------------|------------------|
|          4          |          2          |         2        |         2        |
|---------------------|---------------------|------------------|------------------|
|          5          |          1          |         1        |         1        |
|---------------------|---------------------|------------------|------------------|

The format of the Question sheet is:

|---------------------|---------------------|------------------|
|          ID         |      Question       |       Value      |
|---------------------|---------------------|------------------|
|          1          |      Question1      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question1      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question1      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question1      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question1      |         1        |
|---------------------|---------------------|------------------|
|          1          |      Question2      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question2      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question2      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question2      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question2      |         1        |
|---------------------|---------------------|------------------|
|          1          |      Question3      |         5        |
|---------------------|---------------------|------------------|
|          2          |      Question3      |         4        |
|---------------------|---------------------|------------------|
|          3          |      Question3      |         3        |
|---------------------|---------------------|------------------|
|          4          |      Question3      |         2        |
|---------------------|---------------------|------------------|
|          5          |      Question3      |         1        |
|---------------------|---------------------|------------------|

Formula to populate the ID from the Responses Sheet to the Question Sheet however it increments while I need it to follow the pattern in the Question table:

=IF(INDIRECT("Responses!A:A")=0,"",INDIRECT("Responses!A:A"))

So far I can only get the first 5 responses of each column and I am not sure how to get the header value that corresponds to it in the Question column.

Here is the formula that I have so far to populate the Value column from the Responses sheet however it is limited to the values in 1 column.

=IF(INDIRECT("Responses!B2:B40000")=0,"",INDIRECT("Responses!B2:B40000"))

Thank you!

Answer 1

Since you have a recent version, this should work.

Use 'Data > Get & Transform Data > From Table/Range':

In the Power Query Editor, select the columns with the question answers in them, then use 'Transform > Any Column > Unpivot Only Selected Columns':

Use 'Home > Close & Load' and you should get what you need:

EDIT:

If the final order of the data is imported, you can sort the data in the Power Query Editor just before hitting Close & Load.