0

I have a table like this in Excel:

Row | Question | Answer
1   |    Q1    |   a
1   |    Q2    |   b
1   |    Q3    |   c
2   |    Q1    |   d
2   |    Q2    |   e
2   |    Q3    |   f

and I want to put my questions in the columns, and my answers in the values: E.g., I want my output to be:

Row |  Q1 |  Q2 |  Q3
1   |  a  |  b  |  c
2   |  d  |  e  |  f

How can I do this?

Excellll
  • 12,847
user3652229
  • 1

1 Answers1

1

You are wanting a pivot-table layout (or, if this were a Microsoft Access question, a "crosstab" report), but you don't want any of the summary functionality inherent in pivot tables. It would be nice if Excel pivot tables could provide this option -- but they don't.

INDEX+MATCH can get it done for you, along with an Excel "table" object. See the picture below.

After entering your data, convert it to a table ("Insert", "Table").

List your "Questions" in a row; list your "Rows" down a column. (Note: I took the liberty of renaming your "Row" to "Section" here. I also extended your dataset a bit.)

In cell G4, the formula is

{=IFERROR(INDEX(Table1[Answer],MATCH(G$3 & $F4,Table1[[Question ]] & Table1[Section],0)),"")}

It's an array formula, entered with CTRL+SHIFT+ENTER. Note also the concatenation operator "&" that's used in 2 places. Also note the $ signs, creating absolute references.

Once the array formula has been entered you can drag it across and down.

If you have additional questions or rows to add, just append them to the table object; it will automatically expand to include them. Of course, you'll need to extend your final report's layout to pick up the additions.

enter image description here

F106dart
  • 1,873