+ Reply to Thread
Results 1 to 6 of 6

querying data from a 2 dimensional table

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2013
    Posts
    8

    querying data from a 2 dimensional table

    Hi
    Apologies if this has been posted before somewhere..
    I have a spreadsheet, which has users details on it, plus a list of applications they have in the form of 2 dimensional matrix (apps across the top, and an 'X' marking off whether they have the app). If I wanted to pull out a user, plus a list of their apps on a separate spreadsheet, how would I do this.? I see there is a VLOOKUP you can use with MATCH to look at the data from both dimensions, but I want something in reverse really, i.e. type in a user id, and for it to list the applications beneath.

    I suppose the alternate query for this, is to ask how you would create a flat table from the matrix as described above. Instead of having:


    User App1 App2 App3
    xx999 X X

    I'd like:

    xx999, App1
    xx999, App3

    and so on

    Hope that describes this weird query. If anyone can help let me know.


    John

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: querying data from a 2 dimensional table

    Here's a short subroutine that can do it:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by daffodil11; 01-22-2015 at 02:05 PM. Reason: OOPS.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: querying data from a 2 dimensional table

    Hi, that is brilliant - I just need to reverse what is placed in the table, as the 'X' represents that they have the app, not the other way around. Functionally though it's bang on what I'm after.
    many thanks
    John

  4. #4
    Registered User
    Join Date
    07-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: querying data from a 2 dimensional table

    Hi, that is brilliant - I just need to reverse what is placed in the table, as the 'X' represents that they have the app, not the other way around. Functionally though it's bang on what I'm after.
    many thanks
    John

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: querying data from a 2 dimensional table

    Oops. Just a tiny typo away.

    Put quotes around the x in the code and it works as intended.

  6. #6
    Registered User
    Join Date
    07-06-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: querying data from a 2 dimensional table

    Thanks a million

+ 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. Replies: 0
    Last Post: 04-16-2012, 07:43 PM
  2. [SOLVED] return a value from 2 dimensional table
    By bertique in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-26-2012, 08:35 AM
  3. Querying alternative workbook/imported access table and updating fields accordingly.
    By 5habbaranks in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2010, 08:51 AM
  4. Two dimensional table
    By ElmerS in forum Excel General
    Replies: 2
    Last Post: 03-06-2010, 11:06 AM
  5. [SOLVED] how do i set up a three dimensional table
    By qadie in forum Excel General
    Replies: 4
    Last Post: 11-02-2005, 06:20 PM

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