Custom Sort with wildcard not working

Sierras

New Member
Joined
Sep 23, 2019
Messages
5
I'm trying to make a custom sort based on a contact's management level. Basically sort by their title.
So the list would sort something like this:
Owner, CEO, President, Vice President, CFO,etc...

The list works fine when the title is exactly as in the custom list. But if the title is a little different, like "Vice President of Finance", then it doesn't sort. And using a wildcard "Vice President*" doesn't work either.

Any help appreciated.

Thanks
 

Sierras

New Member
Joined
Sep 23, 2019
Messages
5
Strange that I can get this to work on a report in Access using the "Like" term in a query

Code:
IIf([Title] Like "Owner*",1,IIf([Title] Like "CEO*",2,IIf([Title] Like "CFO*",3,IIf([Title] Like "General Manager*",3,IIf([Title] Like "President*",2,IIf([Title] Like "*Vice President*",4,IIf([Title] Like "*VP*",4,IIf([Title] Like "*Director*",5,IIf([Title] Like "Manager*",6,IIf([Title] Like "*Supervisor*",7,IIf([Title] Like "*Chief*",8,9)))))))))))
But I can't get it to work in an if statement in Excel. And I can't even get more term as it's the max number of nested ifs that can be used.
Code:
=IF(G2="Owner",1,IF(G2="CEO",2,IF(G2="President",2,IF(G2="CFO",3,IF(G2="Vice President*",4,IF(G2="VP",4,IF(G2="Director",5,IF(G2="Manager*",6,10))))))))
I even tried to do it with VBA and it still doesn't work

Code:
    Sub Custom_Sort_Title()    Worksheets("qryContactsAll").Sort.SortFields.Add Key:=Range( _
        "G:G"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Owner,President,CEO,CFO,General Manager,Vice President*,VP,Director,Manager*,Supervisor,Chief", DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("qryContactsAll").Sort
        .SetRange Range("A2:S1049")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    End Sub
So maybe the best way is to do it with vlookup, but I can't get that to work either.

Any sugestions?
 

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top