Picking data based on more than one dropdown options

Hello there,

Yet another obstacle I'm having trouble with, to which I know without a shadow of doubt you guys can help me with.

Any help will be much appreciated.

I am trying to source out a stream of data based on 3 dropdowns in the front sheet (attached). The dropdown can change criteria from user to user, e.g. client one can filter by Region only, Client 2 can filter to show Region and Segment and cluster and finally a Client would want to view the data as is (no Dropdown is selected). I've shortened the data source to view only a couple of lines, but there are 11000 entries. these entries consists of "Open" and "Lost" Values, which is also criteria i guess. Therefore two separate table will have to shown. I have tried to illustrate the result in the "Result" tab. Please do let me know if I'm unclear of what I'm trying to achieve.

I thank you in advance

F

A "prototype" VBA macro which filters data on your selection criteria,

Additional logic is required for "blank" or "all" selection. Results are "DatA" filtered.

RUN on "Front" tab.

Sub FilterX()
'


'
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Dim DD1 As String
Dim DD2 As String
Dim DD3 As String


Application.ScreenUpdating = False

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Front")

With ws2
    DD1 = .Cells(2, "B")
    DD2 = .Cells(3, "B")
    DD3 = .Cells(4, "B")
End With

    ws1.Activate
    With ws1
        Selection.AutoFilter
        Range("A1:P1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=10, Criteria1:=DD1
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=7, Criteria1:=DD2
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=6, Criteria1:=DD3
    End With
    
    Application.ScreenUpdating = False

End Sub

Modified version to copy filtered data to tab "Filter"

Sub Filter_CPX()

'
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet


Dim DD1 As String
Dim DD2 As String
Dim DD3 As String


Application.ScreenUpdating = False

Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Front")
Set ws3 = Worksheets("Filter")

With ws2
    DD1 = .Cells(2, "B")
    DD2 = .Cells(3, "B")
    DD3 = .Cells(4, "B")
End With

    ws1.Activate
    With ws1
        Selection.AutoFilter
        Range("A1:P1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=10, Criteria1:=DD1
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=7, Criteria1:=DD2
        ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=6, Criteria1:=DD3
    End With
    
    ws3.Range("$A$1:$P$12000").ClearContents
    ActiveSheet.Range("$A$1:$P$12000").SpecialCells(xlCellTypeVisible).Copy ws3.Range("A1")
    ws3.Activate
    
    Application.ScreenUpdating = False

End Sub

Hi Thee John,

THank you for your response, is there a way to do this with excel formals? I'm not so good with Macros.

It could be done with formulae but with 11000 + rows it could be slow as you have to select (filter) data based on combinations of 3 (or 4 if we include "Open" /"Lost" as additional ) criteria.

There is no need to "worry" about the macro: once installed you can forget about it.

Ah okay, I'll give this a go then , THanks for you your help.

If you are not opposed to helper columns then we could ctreate columns as follows:

1, 2, 3, 12, 13 ,23,123

which represent choices 1/2/3 on their own , 1 and 2, 1 and 3, 2 and 3 etc. so 6 columns,.

Concatenate the appropriate data in these columns in helper columns and the do "lookups".

The attached is an updated VBA version where I include a blank line representing "All" in the drop downs: we could use "All" with a minor change to the code.

Give it a try with a reasonable set of data.

Thanks John,

This is amazing, this might be a silly quesiton, but how do i assign this marco to my report?

Thanks

Do you mean "how do you include the macro in your workbook"?

Sorry, that's what I meant

If "Developer" is not on the main menu ribbon then:

Right click on "File" ==> "Customize the ribbon" and in the "Main Tabs" window select "Developer".

Back in Excel:

Click "alt+F11" to open the VBA editor

Click "Insert" ==> "Module" and then copy/paste code into blank window on the right.


To add RUN button:

Back in Excel ...

Click "insert" ==> Shapes ... Select a shape

Right-click on shape

"Assign macro" .. Window will open ==> Select macro then "OK"

Right click on Shape ..

"Edit text" ....RUN (or whatever you wish).

Hope this (will) makes sense.

Thanks John much appreciated

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

Hi John,

Quick question regarding the following section of the code you shared with me:
If DD1 <> "" Then
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=10, Criteria1:=DD1
Else
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=10
End If

If DD2 <> "" Then
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=7, Criteria1:=DD2
Else
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=7
End If

If DD3 <> "" Then
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=6, Criteria1:=DD3
Else
ActiveSheet.Range("$A$1:$P$12").AutoFilter Field:=6
End If

can you tell me what this section is doing please?

Thanks very much

ah ok and the rang $A$1:$P$12 refers to the raw data in the data tab?I got confused because i had the range as A1:P14

the A1:P14 is right for the sample: I obviously hadn't changed the macro.

You could make "Data" a dynamic name range and replace the $A$1:$P$12 reference with the named range

e.g range("Data_Rng") where "Data_Rng" is the named range e.g A1:P10000 (static)..

fantastic, thanks John, I've learnt so much today. I need a good VBA tutorial haha