Filter Results Based on Multiple Drop Down Selections

[alyssaccasey]

Hi- I'm looking for a way to show results based on multiple drop down selections and ALSO include all results if one (or more) of the fields is left blank. I'm fine with using a macro is that's necessary. In the below instance, I selected a country, city, currency, and region, and wanted all the company names that match those criteria. I also want it to be able to return all values even if one (or more) of the search criteria are blank (i.e. if I only fill in USA for the country, there should be three company names that are returned). Thanks in advance for your help!!!

 

[Fluff]

Hi & welcome to MrExcel.
You can use advanced filter for that. Excel Advanced Filter – how to create and use
or Excel Advanced Filter Introduction

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book4
	A	B	C	D	E	F	G
1		Country	City	Currency	Region		Company Name2
2	Drop-Down Filters:	USA	Miami	USD	North America		Coca Cola
3							Amazon
4
5	Country	City	Currency	Region	Company Name2
6	USA	Miami	USD	North America	Coca Cola
7	USA	Miami	USD	North America	Amazon
8	USA	New York	USD	North America	Toyota
9	Ireland	Dublin	Euro	Europe	Chase
10	Ireland	Dublin	Euro	Europe	Dell
11	Australia	Melbourne	UAD	APAC	Apple
12	Poland	Warsaw	PLN	Europe	Lowe's
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=FILTER(E6:E12,((A6:A12=B2)+(B2=""))*((B6:B12=C2)+(C2=""))*((C6:C12=D2)+(D2=""))*((D6:D12=E2)+(E2="")))
Dynamic array formulas.

 

[veyselemre]

Sub Advanced_Filtering()
'https://www.goskills.com/Excel/Resources/Excel-VBA-advanced-filter
    CriteriaLastRow = 3

    For i = 2 To CriteriaLastRow
        RowsCount = Application.WorksheetFunction.CountA(Range("B" & i & ":F" & i))
        If RowsCount = 0 Then CriteriaRowsSet = i - 1 Else CriteriaRowsSet = CriteriaLastRow
    Next i

    Range("A4:E" & Rows.Count).AdvancedFilter _
            Action:=xlFilterInPlace, _
            CriteriaRange:=Range("B1:F" & CriteriaRowsSet)
End Sub

 

[alyssaccasey]

Welcome to the MrExcel forum!

Try:

Book4
	A	B	C	D	E	F	G
1		Country	City	Currency	Region		Company Name2
2	Drop-Down Filters:	USA	Miami	USD	North America		Coca Cola
3							Amazon
4
5	Country	City	Currency	Region	Company Name2
6	USA	Miami	USD	North America	Coca Cola
7	USA	Miami	USD	North America	Amazon
8	USA	New York	USD	North America	Toyota
9	Ireland	Dublin	Euro	Europe	Chase
10	Ireland	Dublin	Euro	Europe	Dell
11	Australia	Melbourne	UAD	APAC	Apple
12	Poland	Warsaw	PLN	Europe	Lowe's
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=FILTER(E6:E12,((A6:A12=B2)+(B2=""))*((B6:B12=C2)+(C2=""))*((C6:C12=D2)+(D2=""))*((D6:D12=E2)+(E2="")))
Dynamic array formulas.

Thank you!!!!!

 

[Eric W]

Happy we could help! Thanks for the feedback.

 

[Eric W]

And a bit more obscure, but here's a shorter way to write that formula:

=FILTER(E6:E12,MMULT((A6:D12=B2:E2)+(B2:E2=""),{1;1;1;1})=4)