Issue with Advanved Filter Wildcard! please help!

[cocorico]

Hi all,
I am working on my advanced filter. I would like to find all date records that contain 2014. The issue is that some records contain Q1 2014, Q2 2014, etc and others have 2014 strictly as date.
If i type in "2014" in the crteria ecxel will return all 2014 records BUT NOT q1 2014 nor q2 2014.
If i type in *2014 excel returns q1 2014, q2 2014 BUT NOT 2014.
Can somebody help? It's quite urgent! Please do not come up with an autofilter solution, i am preparing a big advanced filter solution.
Cheers

 

[RoryA]

You'd need to use two rows - one with 2014 and one with "*2014". If you have other criteria required at the same time, they will need to be duplicated across both rows. You could also use a formula criteria range.

 

[cocorico]

You'd need to use two rows - one with 2014 and one with "*2014". If you have other criteria required at the same time, they will need to be duplicated across both rows. You could also use a formula criteria range.

Hi Rory,
Thank you very much for your help. I thought about the two rows option, in fact in my case it would be 2 times 8 rows as other crtieria would need to be duplicated too.
What is the criteria formula range?
Thank you.

 

[RoryA]

In your criteria range, instead of using a column titled "Date" (or whatever your field name is), leave the header cell blank, and use a formula like:
=ISNUMBER(SEARCH("2014",A6))
where A6 is the first row of value data (not the header) for the date field in your data table. So for the example below the criteria range is A1:C2.

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]Field2[/td][td]Field3[/td][/tr]

[tr][td]

2​

[/td][td]

=ISNUMBER(SEARCH("2014",A6))​

[/td][td]blah[/td][td]test[/td][/tr]

[tr][td]

3​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Date[/td][td]Field2[/td][td]Field3[/td][/tr]

[tr][td]

6​

[/td][td]

2014​

[/td][td]blah[/td][td]test[/td][/tr]

[tr][td]

7​

[/td][td]

2013​

[/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Q4 2014[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Q4 2013[/td][td]blah[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Q1 2013[/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Q2 2012[/td][td]blah[/td][td]test[/td][/tr]

[tr][td]

12​

[/td][td]Q3 2014[/td][td]blah[/td][td][/td][/tr]
[/table]

 

[cocorico]

Great! That works! Thank you so much for your help!