Double filter or lookup and filter

[Opy]

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:

Config_Item	Environment	Class	CI_Type		Responsibility	Contact
ACCT		Production	Software	Application	Business Owner	Angus Boneparte
ACCT		Production	Software	Application	Business Owner	Paul McCarthy
ACCT		Production	Software	Application	System Owner	Robert Freelove
ACCT DEV		Development	Software	Application	System Owner	Billy Kee
ACCT DEV		Development	Software	Application	Business Owner	Edward Teach
ACCT TEST		Testing		Software	Application	System Owner	Billy Kee
ACCT TEST		Testing		Software	Application	Business Owner	Edward Teach
ACCT UAT		UAT		Software	Application	System Owner	Billy Kee
ACCT UAT		UAT		Software	Application	Business Owner	Miriam Jones

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:

Config_Item	Environment	Class	CI_Type		Responsibility	Contact
ACCT DEV		Development	Software	Application	System Owner	Billy Kee
ACCT DEV		Development	Software	Application	Business Owner	Edward Teach
ACCT TEST		Testing		Software	Application	System Owner	Billy Kee
ACCT TEST		Testing		Software	Application	Business Owner	Edward Teach

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.

[nimrod]

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

[Opy]

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.

[nimrod]

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 ?

[Opy]

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.

Config_Item	Environment	Class	CI_Type		Responsibility	Contact

ACCT		Production	Software	Application	Business Owner	Angus Boneparte
ACCT		Production	Software	Application	Business Owner	Paul McCarthy
ACCT		Production	Software	Application	System Owner	Robert Freelove
ACCT DEV		Development	Software	Application	System Owner	Billy Kee
ACCT DEV		Development	Software	Application	Business Owner	Edward Teach
ACCT TEST		Testing		Software	Application	System Owner	Billy Kee
ACCT TEST		Testing		Software	Application	Business Owner	Edward Teach
ACCT UAT		UAT		Software	Application	System Owner	Billy Kee
ACCT UAT		UAT		Software	Application	Business Owner	Miriam Jones

Have I helped clarify the base data for you?

[nimrod]

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

Option Base 1
Public Sub FilterByVisibleContacts()
  Dim myArray()
  
  For Each VisibleCell In Range("A:A").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 2)
     lp = lp + 1
      ReDim Preserve myArray(lp)
      myArray(lp) = VisibleCell.Value
  Next VisibleCell
  
  ActiveSheet.ShowAllData
  ActiveSheet.Range("$A:$F").AutoFilter Field:=1, Criteria1:=myArray, Operator:=xlFilterValues
End Sub

[Opy]

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.

[nimrod]

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

Option Base 1
Public Sub FilterByVisibleContacts()
  Dim myArray()
  Dim DoneRng As Range
  
   ReDim Preserve myArray(1)
  
  For Each VisibleCell In Range("A:A").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 2)
     lp = lp + 1
     
     If NewValue(myArray, VisibleCell.Value) Then
        ReDim Preserve myArray(lp)
        myArray(lp) = VisibleCell.Value
     End If
  Next VisibleCell
  
  
  ActiveSheet.ShowAllData
  ActiveSheet.Range("$A:$F").AutoFilter Field:=1, Criteria1:=myArray, Operator:=xlFilterValues
  
  Set DoneRng = Nothing
End Sub

Public Function NewValue(myArray, CurrValue As String) As Boolean
   NewValue = True
   For i = 1 To UBound(myArray)
      If myArray(i) = CurrValue Then
         NewValue = False
         Exit Function
      End If
   Next i
End Function

[Opy]

"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.

[Opy]

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.

Option Base 1

Sub Unfilter()

    ' Removes all filters from the page to reset the report.

    If ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    
    Range("A5").Select
    
End Sub


Public Sub FilterByVisibleContacts()
  Dim myArray()
  
  Application.ScreenUpdating = False
  On Error Resume Next 'to bypass error from lazy method used to designate a range. 
  
   ReDim Preserve myArray(1)
  lp = 0
  
  For Each VisibleCell In Range("A5:A10000").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 2)
     lp = lp + 1
     
     If NewValue(myArray, VisibleCell.Value) Then
        ReDim Preserve myArray(lp)
        myArray(lp) = VisibleCell.Value
     End If
  Next VisibleCell
  
  For x = 1 To lp
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$A5:$G10000").AutoFilter Field:=1, Criteria1:=myArray(x)
    Range("G4").Select
        For Each VisibleCell In Range("A5:A10000").SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants, 2)
        
        ActiveCell.Offset(1, 0).Select
        Do Until ActiveCell.EntireRow.Hidden = False
            ActiveCell.Offset(1, 0).Select
        Loop
        ActiveCell.Value = "*"
        Next VisibleCell

  Next x
 
    ActiveSheet.ShowAllData
    
    ActiveSheet.Range("$A$4:$G$10000").AutoFilter Field:=7, Criteria1:="<>"
    Range("A4").Select
    ActiveCell.Offset(1, 0).Select
    
    Range("G5:G10000").ClearContents

    Application.ScreenUpdating = True
    
End Sub

Public Function NewValue(myArray, CurrValue As String) As Boolean
   NewValue = True
   For i = 1 To UBound(myArray)
      If myArray(i) = CurrValue Then
         NewValue = False
         Exit Function
      End If
   Next i
End Function