# Re-Arranging Data Tables. From Dropdown to Headers

• 09-17-2017, 12:22 PM
oblah
Re-Arranging Data Tables. From Dropdown to Headers
Hi all,

I need to re-format a table.

Currently it is setup as the following:

I would like it to be formatted as:

Company ||Question 1||Question 2

I am after this re-formatting to enable index match look-ups.

Is there a way for Excel to re-format a table?

Kind Regards

Oblah
• 09-17-2017, 02:57 PM
oeldere
Re: Re-Arranging Data Tables. From Dropdown to Headers
and answer 3 is not needed?

it is important to get the criteria correct.
• 09-17-2017, 03:22 PM
oblah
Re: Re-Arranging Data Tables. From Dropdown to Headers
Criteria has been edited.
• 09-18-2017, 07:52 PM
JeteMc
Re: Re-Arranging Data Tables. From Dropdown to Headers
This proposed solution employs three array entered formulas*.
(Yellow) The formula that displays the questions is:
Formula:
`=IFERROR(INDEX(\$B\$8:\$B\$26, MATCH(0, COUNTIF(\$E\$8:E8, \$B\$8:\$B\$26), 0)),"")`

(Green) The formula that displays the companies is:
Formula:
`=IFERROR(INDEX(\$A\$8:\$A\$26, MATCH(0, COUNTIF(\$E\$8:E8, \$A\$8:\$A\$26), 0)),"")`

(Blue) The formula that displays the answers is:
Formula:
`=IFERROR(INDEX(\$C\$8:\$C\$26, SMALL(IF(\$B\$8:\$B\$26=F\$8,IF(\$A\$8:\$A\$26=\$E9,ROW(\$8:\$26)-7)),1)),"")`

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Conditional formatting is applied to the range E8:I11 which will hide the zeros once the fill color is removed.
Let us know if you have any questions.
• 10-03-2017, 11:52 AM
oblah
Re: Re-Arranging Data Tables. From Dropdown to Headers
JeteMC saving the day once again.
*Apologies for the late response, been on holiday :) *

Thank you very much for your help.

This has has helped with my progression of my tool.

Are you able to provide some clarity on the last part of the formula relating to the Answers.

"IF(\$A\$8:\$A\$26=\$E9,ROW(\$8:\$26)-7)),1)),"")"

In my actual workbook, the non-formatted data and the formatted data are located on different tabs.

I'm assuming this affects the code attached.

Would ROW(\$8:\$26)-7)),1,"")" instead reference all of the data from my non-formatted table?

Kind Regards

Oblah
• 10-17-2017, 09:21 AM
JeteMc
Re: Re-Arranging Data Tables. From Dropdown to Headers
Been on holiday myself.
to reference the non formatted table from sheet 1 the array entered formula (see post #4) for the answers could read:
Formula:
`=IFERROR(INDEX(Sheet1!\$C\$8:\$C\$26, SMALL(IF(Sheet1!\$B\$8:\$B\$26=F\$8,IF(Sheet1!\$A\$8:\$A\$26=\$E9,ROW(\$8:\$26)-7)),1)),"")`

In the attached copy of the file the formatted table is on sheet 2.
Let us know if you have any questions.