Advanced filters - variable dynamic criteria filtering

[IanPM]

Data	Filter selection
	Jan
Jan
NowJanuary
Feb
RatJan
Febby
NowFeb

<tbody>
</tbody>

Hi
Want to use Advanced filter whereby it filters using part supplied selection entered in the same sheet finding & filtering to all data that include this partial data

Based on example if want to filter by Jan It must filter to display
Jan
NowJanuary
RatJan

If filter selection is feb it must filter to
Feb
Febby
NowFeb

So I looking for dynamic selection option that can filer based on any part of the name that meets that criteria whatever the criteria is. It must not be case sensitive

 

[Marcelo Branco]

You can use Advanced Filter with a formula as criteria.

Something like this

	A​	B​	C​
1​			Filter selection​
2​			Jan​
3​
4​	Data​		Formula​
5​	Jan​		TRUE​
6​	NowJanuary​
7​	Feb​
8​	RatJan​
9​	Febby​
10​	NowFeb​

<tbody>
</tbody>

In C5 insert this formula
=ISNUMBER(SEARCH($C$2,A5))

Select data range (A4:A10) and apply Advanced filter using
Criteria Range:$C$4:$C$5

Hope this helps

M.

 

[IanPM]

Re: Advanced filters_VBA - variable dynamic criteria filtering

I have updated the file with information & got it to work up to a point but the filter only work if the data starts with the criteria. I tried to adapt it to below but could not get it to work. From information below you will be able to see the filter proses for criteria entry & where the dynamic data comes in for the filter. In this example it only filers to everything stating with "AFR" not everything that contains "AFR"

VBA Code used ( A12= Country; h5:k6 = criteria range )

Range("A12").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("h5:k6"), Unique:=False


Dynamic values formula ( Cell C6 = Account name )

=IF(C6="","*",C6)

<tbody> </tbody> Summary / Evaluation Overview <tbody> </tbody>	Based on this input	Currently it only filter to fields starting with "AFR", ignoring the rest
Selections					It must filter to this
Filter options​				Criteria - In sheet filter
Country​	Account #​	Account name	Reference​				Country​	Account #​	Account name​	Reference​
		AFR					*	*	AFR	*
Summary selection for filtered information​
​	​		​	2018​
Subtotal​	​		​	18,332 ​
​	​		​
Country​	Account #​	Account name	Reference​	Val1
ZA​	10001​	African-East	AA​	500
ZA​	10001​	African-East	AB​	550
ZA​	10001​	African-East	AC​	605
ZA​	10001​	African-East	AD​	666
ZA​	10002​	East-Africa	AA​	600
ZA​	10002​	East-Africa	AB​	660
ZA​	10002​	East-Africa	AC​	726
ZA​	10002​	East-Africa	AD​	799
ZA​	10003​	AFR	AA​	850
ZA​	10003​	AFR	AB​	935
ZA​	10003​	AFR	AC​	1,029
ZA​	10003​	AFR	AD​	1,131
ZA​	10004​	WEST	AA​	900
ZA​	10004​	WEST	AB​	990
ZA​	10004​	WEST	AC​	1,089
ZA​	10004​	WEST	AD​	1,198
ZA​	10005​	WEST -1	AA​	1,100
ZA​	10005​	WEST -1	AB​	1,210
ZA​	10005​	WEST -1	AC​	1,331
ZA​	10005​	WEST -1	AD​	1,464

<tbody> </tbody>

 

[Marcelo Branco]

Re: Advanced filters_VBA - variable dynamic criteria filtering

Try this formula in J6
=IF(C6="","*","*"&C6&"*")

So you see in H5:K6 (Criteria Range)

	H​	I​	J​	K​
5​	Country​	Account #​	Account name​	Reference​
6​	*​	*​	*AFR*​	*​

<tbody>
</tbody>

Hope this helps

M.

 

[IanPM]

Re: Advanced filters_VBA - variable dynamic criteria filtering

Thank you looks good.