+ Reply to Thread
Results 1 to 10 of 10

Double filter or lookup and filter

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Double filter or lookup and filter

    This is hard for me to explain to me let alone to you, so good luck and thanks in advance for considering solutions.

    I'm using Excel 2007.
    This question is not quite code based, I'm more looking for ideas on what direction I should try to go with the code, focussing on efficiency.

    From an SQL Query I get a table like this showing me who the owners are of my CMDB items:

    Please Login or Register  to view this content.

    I want to select a name via drop down list and display all owners of all items of which the selected person is an owner.
    eg. If I select "Edward Teach" it will lookup all items linked to Edward and display all rows for those items and I would get this result:

    Please Login or Register  to view this content.

    I'll have 1 button to run the SQL Query and import the data. (I have already written the code for that). After that I don't want the spreadsheet constantly looking at the database, I want it to only use what has already been imported.

    I will probably add to the data import script the creation of an alphabetized list of a unique names on a seperate worksheet to use for the Drop Down list. That should be easy enough.

    But I need ideas on how to go about this double filter.

    Here is my current concept. Can you offer a more efficent method for me?

    Upon selecting a name it could run some sort of lookup and physically create/replace a table showing all Config_Items associated with the name. Then it could use the data from that table to do a standard multi entry autofilter.

    I've not previously tried initiating a macro from a drop down selection. I think I can or perhaps I'll need to add a [Search] button that will actually run it.
    Last edited by Opy; 03-16-2011 at 01:02 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Double filter or lookup and filter

    If I select "Edward Teach" it will lookup all items linked to Edward and display all rows for those items and I would get this result:
    Sorry but in your example the "results" section shows more contact's than Edward Teach... am I missing something

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Re: Double filter or lookup and filter

    Edward Teach is an owner of ACCT DEV and of ACCT UAT.
    I want to see all owners of these items not just the person selected. So Billy Kee who is also an owner of ACCT DEV and of ACCT UAT will be shown when doing the report for Edward.

    If I select your name, I want to know what items you own and I want to know all of the other owners for those items.

    I hope that makes sense.

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Double filter or lookup and filter

    So I'm assuming what the people own is column "config item" . But I don't see any column that tells me what "config items" are owned by who. For example , I'm not seeing anything that tells me Edward is an owner of Acct Dev or Acct Test. Is there another table with this information ?

  5. #5
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Re: Double filter or lookup and filter

    Sorry Nimrod. I get embarrassed when I am bad at explaining myself. I should be good at it by now.

    It is all in this one table.

    The Contact column is the owner.
    Responsibility is the type of ownership.
    We have a few different types of ownership, which is indicated in the Responsibility column.
    Each Config_Item is listed as many times as there are Contacts for that item.
    So "ACCT DEV" has Edward Teach as the Business Owner and also has Billy Kee as the System Owner.
    As you can see for "ACCT" there are 2 Business Owners and 1 System Owner. This is common practice for the larger applications.

    Please Login or Register  to view this content.
    Have I helped clarify the base data for you?

  6. #6
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Double filter or lookup and filter

    Here's one solution...
    (1) have data on active sheet , with CofigItem in Column A , and Contacts in Column F... etc etc

    (2) Turn on AutoFilters and then Filter by Column F ... you may filter by a single contact or by many

    (3) While still filtered Run the following Macro

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by nimrod; 03-15-2011 at 08:26 PM.

  7. #7
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Re: Double filter or lookup and filter

    That seems a lot simpler that the way I was planning to do it. Exactly why I asked

    Thank you very much Nimrod. That may be exactly what I'm after.

  8. #8
    Valued Forum Contributor
    Join Date
    09-21-2003
    Location
    British Columbia , Canada
    MS-Off Ver
    03,07,10,13
    Posts
    727

    Re: Double filter or lookup and filter

    VERSION (2)

    Hello Opy:
    This version does things a little cleaner... It examines what values are already in the Array to make sure each value is only entered once.

    ..... enjoy


    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Re: Double filter or lookup and filter

    "Operator:=xlFilterValues" won't work in earlier versions of Excel which some users may be using.
    Works great in 2007, thanks again Nimrod.
    I'll do some more research and post a new topic if I can't find an answer.

  10. #10
    Registered User
    Join Date
    08-04-2010
    Location
    Sydney
    MS-Off Ver
    Excel 2003,2007
    Posts
    30

    Re: Double filter or lookup and filter

    Just adding this to close the thread with my final solution.

    This method will put an * into Column G for each visible item after filtering Contacts.
    It will remove the markers to prevent an error occurring when users run it a 2nd time without removing the filters.

    Not very efficient when a Stakeholder has a lot of items, but this seems to work okay in 2010, and should work for 2003 as well.
    Column headings are in Row 5 now due to user instructions and buttons being at top of page.
    Column G is not hidden, but is very small.


    The top macro, Unfilter, simply removes all filters to reset the report.

    Please Login or Register  to view this content.

+ 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