1

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!

  • Welcome to SU. What version of Excel are you using? – FlexYourData Jun 3 '20 at 19:48
  • 1
    Thanks! I am using Microsoft Excel for Microsoft 365 MSO (16.0.12730.20252) 64-bit, Version 2004 (Build 12730.20270)(May 21st, 2020). – DotNetFullStack Jun 3 '20 at 20:44
  • Hard to tell what you're after here, since you haven't asked a question (this is also not about formatting), but I'd take a stab - Are you trying to populate a Response sheet from the data in the Question sheet? That would be a simple INDEX(MATCH()) in the cells of the body of the table in the Response sheet... it would also be helpful if you edit your question to include your question – Alex M Jun 3 '20 at 21:13
0

Since you have a recent version, this should work.

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

enter image description here

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

enter image description here

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

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.