+ Reply to Thread
Results 1 to 8 of 8

Extract data from a Table

  1. #1
    Registered User
    Join Date
    06-23-2013
    Location
    Colombo, Sri lanka
    MS-Off Ver
    Excel 2007
    Posts
    13

    Extract data from a Table

    Guys I need help in extracting data from a table.


    Excel pic.jpg
    eg.

    A-team B-team D-team C-team
    sam X X
    pat X X
    john X X
    peter X

    I want to know what are the teams in which sam a member. Peoples' names should be selected from a dropdown menu and names of the teams should be displayed below the menu in alphabatical order.

    Can u help me?

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Extract data from a Table

    Here's a formulas play to deliver your intents ...

    Assume your source table as pictured is in A1:E5
    names in A2:A5, Team names in B1:E1, with "x" inputs within B2:E2

    In H1: =INDEX($A$2:$A$5,COLUMNS($A:A))
    Copy across to K1
    In H2: =IF(OFFSET($B$2,COLUMNS($A:A)-1,ROWS($1:1)-1)="x",ROWS($1:1),"")
    Copy across to K2, fill down to K5

    Now, assume you have a DV list in A8 to select the name, eg: sam, pat, etc
    Put in A9:
    =IF(ROWS($1:1)>COUNT(OFFSET($G$2:$G$5,,MATCH($A$8,$H$1:$K$1,0))),"",INDEX($B$1:$E$1,SMALL(OFFSET($G$2:$G$5,,MATCH($A$8,$H$1:$K$1,0)),ROWS($1:1))))
    Copy down to A12. This returns the results (ie the Team names) for the name selected in A8, all neatly packed at the top. Select another name from the DV to test it out, also test by re-doing the "x" inputs within B2:E5, it should work fine
    ------------------------------------------------
    Success? Wave it, click on the little star at the bottom left of my responses

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Extract data from a Table

    Pl see attached file.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Extract data from a Table

    Reply to post #3
    ... Believe your startpoint as to the source data layout differs considerably from what the OP presented in the jpg (Excel pic.jpg) ?

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Extract data from a Table

    Pl see attached file.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-23-2013
    Location
    Colombo, Sri lanka
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extract data from a Table

    Guys th ank you very much for help.
    further, can we get the results other way around also? I mean if i give the team can i get the members by changing the formula?

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Extract data from a Table

    Pl see attached file.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-23-2013
    Location
    Colombo, Sri lanka
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Extract data from a Table

    Thanks friend

+ 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. Search for Excel file and the sheet within that from the given table data and extract data
    By adrian_slash in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-23-2013, 07:55 AM
  2. Extract Data from one table to another without blanks
    By pilot_guy2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2012, 07:50 PM
  3. extract data from a simple table
    By lee_har in forum Excel General
    Replies: 3
    Last Post: 02-04-2012, 04:21 AM
  4. Extract/copy data from a table
    By razaas in forum Excel General
    Replies: 10
    Last Post: 01-04-2010, 07:55 AM
  5. Need macro to extract data from 2D table
    By wingie in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-22-2008, 01:34 AM

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