Re-Arranging Data Tables. From Dropdown to Headers

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

Re: Re-Arranging Data Tables. From Dropdown to Headers

and answer 3 is not needed?

it is important to get the criteria correct.

Re: Re-Arranging Data Tables. From Dropdown to Headers

Criteria has been edited.

1 Attachment(s)

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.

Re: Re-Arranging Data Tables. From Dropdown to Headers

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

1 Attachment(s)

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.