+ Reply to Thread
Results 1 to 5 of 5

Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

    Hi Experts,

    I have a matrix of about 71 x 60 (in actual). I need to convert it to table form. see the attached file and image for the required output.

    I'm not getting the output in right format which I require. The matrix has two headers like row 3 & row 4 in this example. I need your help in this matter.



    Thanks in advance.


    2020-09-03_10-54-22.png
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

    I dont think this is possible using a formula as you have merged columns.
    You may be able to use a formula if you have the same number of columns per heading, ie
    11 columns for Areas
    11 columns for Head
    11 columns for Eye
    etc

    However, this could cause complications
    First, you would have blank unused columns

    Secondly, to extract the heading name you would need to place the name linearly in each header group, e.g.
    Areas has 11 columns and "Areas" is in the first column of that group.
    "Head" is in the first column of the Head group.
    "Eye is in the first column of the Eye group
    etc
    You could then use one formula to extract the heading names as you know the heading name is an equal amount of columns away, e.g. column 1, column 12, column 23 (every 11 columns)

    Thirdly, if you add or delete a column for a header group you would have to reflect that column addition/deletion in the other columns or the formula that extracts headings would be out, ie delete a column from Areas so it now contains 10 columns then you would have to delete a column from the other headers of Head, Eye etc so they contain 10 columns.

    Based on this, you would be better of using VBA.
    Last edited by Special-K; 09-03-2020 at 05:19 AM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

    @Special-K, I get your point.

    as far VBA, would you please help me out in this?

  4. #4
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

    any help from vba experts?

  5. #5
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Matrix to Table - Index/Match return multiple column header(s) [multiple occurrences]

    I need helping hand ! !!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Array formula to match value to row and pull row header (multiple occurrences of value)
    By lbdavis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2020, 01:34 PM
  2. Index/Match across multiple sheets, return column header
    By TheYoungDrea in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-09-2017, 07:49 AM
  3. [SOLVED] INDEX, MATCH multiple values in single row, return header value(s)
    By grrliz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2014, 07:21 PM
  4. VBA index match with multiple occurrences
    By jwlamb in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 11-26-2014, 11:04 PM
  5. [SOLVED] Index & Match for multiple Column and Row to find header...
    By mr-c in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2014, 08:24 AM
  6. [SOLVED] return multiple values in 1 column via index / match
    By Bax in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-12-2013, 06:54 AM
  7. Replies: 3
    Last Post: 06-15-2012, 04:19 PM

Tags for this Thread

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