+ Reply to Thread
Results 1 to 6 of 6

Re-Arranging Data Tables. From Dropdown to Headers

  1. #1
    Registered User
    Join Date
    06-27-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    13

    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
    Last edited by oblah; 09-17-2017 at 03:21 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    12,322

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

    and answer 3 is not needed?

    it is important to get the criteria correct.
    Notice my main language is not English.

  3. #3
    Registered User
    Join Date
    06-27-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    13

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

    Criteria has been edited.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,597

    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: copy to clipboard
    Please Login or Register  to view this content.

    (Green) The formula that displays the companies is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (Blue) The formula that displays the answers is:
    Formula: copy to clipboard
    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    06-27-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    13

    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

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    7,597

    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: copy to clipboard
    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1