Advanced Filter

[MRDecarte]

How would you set up advanced filter if you want to collect records of a dataset that contain numbers stored as text, and you want them starting with the partial text 646&"*" or 647&"*". I have a dataset where a column contains account code in text format such 6****** and I want all of those records starting with 646 and 647.

I know how to do it with custom filter in the column using begins with or begins with but I cannot get it to work using advanced filter.

Thank you

 

[Akuini]

Did you try: 646*
if your number is stored as text (not just formatted as text) then it should work.

 

[MRDecarte]

Thanks Akuini but it does not work. The number are stored as text I believe as aligned to the left and when using the function ISTEXT I get a TRUE.
I get all account codes 643...647 etc unfortunately. Thanks though.

 

[Akuini]

Not sure why it didn't work.
Here's what I got using this code:

Sub Macro11()
    Range("Table1[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("A1:D2"), Unique:=False
End Sub

Excel 2013 32 bit
[Table="width:, class:head"][tr=bgcolor:#008B8B][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

1​

[/td][td=bgcolor:#5B9BD5]STATE[/td][td=bgcolor:#5B9BD5]CITY[/td][td=bgcolor:#5B9BD5]REP[/td][td=bgcolor:#5B9BD5]no[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

2​

[/td][td][/td][td][/td][td][/td][td]1*[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

3​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

4​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

5​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

6​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

7​

[/td][td=bgcolor:#5B9BD5]STATE[/td][td=bgcolor:#5B9BD5]CITY[/td][td=bgcolor:#5B9BD5]REP[/td][td=bgcolor:#5B9BD5]no[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

8​

[/td][td=bgcolor:#DDEBF7]Arkansas[/td][td=bgcolor:#DDEBF7]Conway[/td][td=bgcolor:#DDEBF7]Quincy[/td][td=bgcolor:#DDEBF7]1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

17​

[/td][td]Arkansas[/td][td]Springdale[/td][td]Truman[/td][td]10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

18​

[/td][td=bgcolor:#DDEBF7]Arizona[/td][td=bgcolor:#DDEBF7]Tucson[/td][td=bgcolor:#DDEBF7]Mario[/td][td=bgcolor:#DDEBF7]11[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

19​

[/td][td]Arizona[/td][td]Tucson[/td][td]Mark[/td][td]12[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

20​

[/td][td=bgcolor:#DDEBF7]Arkansas[/td][td=bgcolor:#DDEBF7]Jonesboro[/td][td=bgcolor:#DDEBF7]Makai[/td][td=bgcolor:#DDEBF7]13[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

21​

[/td][td]California[/td][td]Chula Vista[/td][td]Zachariah[/td][td]14[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#008B8B]

22​

[/td][td=bgcolor:#DDEBF7]California[/td][td=bgcolor:#DDEBF7]Chula Vista[/td][td=bgcolor:#DDEBF7]Zander[/td][td=bgcolor:#DDEBF7]15[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[Akuini]

Here's the file:

https://www.dropbox.com/s/ne3ojbwoqkc5jqv/try - advance filter.xlsm?dl=0

 

[MRDecarte]

Great many thanks for the macro and solution! It is a nice peace of code
I can get it to work with the following formula but I find it strange that it is not working with your initial proposal 646* or 647* with the corresponding field name included in the advanced filter procedure.

OR(LEFT(I3,3)="646",LEFT(I3,3)="647")

Many thanks Akuini

 

[Akuini]

Hm, it's strange you can't get it to work.
I'm using Excel 2013, what's yours?
But did you download my file & tried it, does it work?