+ Reply to Thread
Results 1 to 6 of 6

Extract table headers & corresponding Column Values based on a condition

  1. #1
    Registered User
    Join Date
    05-21-2014
    Location
    Saudi Arabia
    MS-Off Ver
    2021
    Posts
    38

    Extract table headers & corresponding Column Values based on a condition

    Hi,

    I need to extract the table header values & corresponding column values based on where the conditions meet.

    in this particular example, wherever a cell value is "N", the output should return the corresponding header Value and the name of the student.


    I've attached the workbook, you help is really appreciated. Thanks


    2020-11-08_15-06-50.png
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract table headers & corresponding Column Values based on a condition

    What version of Excel are you using? Please amend your profile to show this. It would also be helpful if you showed, even in general terms, where you are located (e.g. country).
    Last edited by Glenn Kennedy; 11-08-2020 at 08:43 AM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract table headers & corresponding Column Values based on a condition

    Assuming you have Excel 2010 or later:

    In W2, copied down:

    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$5)/((ISNUMBER(SEARCH($X$1,$C$2:$T$5)))*(COUNTIF(W$1:W1,$B$2:$B$5)=0)),1)),"")

    In X2, copied across and down:

    =IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($C$1:$T$1)/(($B$2:$B$5=$W2)*($C$2:$T$5=$X$1)),COLUMNS($X:X))),"")
    Attached Files Attached Files

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Extract table headers & corresponding Column Values based on a condition

    Array formulas
    Please Login or Register  to view this content.
    And
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-21-2014
    Location
    Saudi Arabia
    MS-Off Ver
    2021
    Posts
    38

    Re: Extract table headers & corresponding Column Values based on a condition

    Thank you to both of you Glenn Kennedy and BMV,

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Extract table headers & corresponding Column Values based on a condition

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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] Extract every 2nd row data based value not blank and criteria in column headers
    By PaulM100 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-09-2020, 01:52 AM
  2. [SOLVED] Generate unique list of values based on condition in another table column with VBA
    By dabasir in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2019, 09:12 PM
  3. [SOLVED] Extract subarray from table column based on criteria matched - text values
    By jaryszek in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-31-2018, 03:35 AM
  4. Extract and Merge Data based on Column Headers
    By Krix in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-25-2017, 02:08 PM
  5. Need help pulling column headers based on table values (not max or min)
    By red5030 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-08-2014, 10:45 AM
  6. [SOLVED] How to select from a table of values based upon column and row headers
    By Peteryoull in forum Excel General
    Replies: 4
    Last Post: 07-30-2012, 05:58 AM
  7. [SOLVED] How do I: Extract column headers based on lowest number in sequence
    By WolfJack in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-21-2006, 04:30 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