Re-Arranging Data Tables. From Dropdown to Headers

[oblah]

Hi all,

I need to re-format a table.

Currently it is setup as the following:

Company 1||Question 1||Answer 1
Company 1||Question 2||Answer 2
Company 2||Question 1||Answer 1
Company 2||Question 2||Answer 2

I would like it to be formatted as:

Company 1||Answer 1 ||Answer 2
Company 2||Answer 1 ||Answer 2
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

[oeldere]

and answer 3 is not needed?

it is important to get the criteria correct.

[oblah]

Criteria has been edited.

[JeteMc]

This proposed solution employs three array entered formulas*.
(Yellow) The formula that displays the questions is:

Please Login or Register  to view this content.

(Green) The formula that displays the companies is:

Please Login or Register  to view this content.

(Blue) The formula that displays the answers is:

Please Login or Register  to view this content.

*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.

[oblah]

JeteMC saving the day once again.
*Apologies for the late response, been on holiday *

Hi Sir/Madam,

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

[JeteMc]

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:

Please Login or Register  to view this content.

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