Alphabetical Sort but ignore square brackets

GordonMasson
GordonMasson used Ask the Experts™
on
I have a worksheet with various columns of data and I want to be able to sort alphabetically by column A.
The problem is that I have certain entries that I want to be listed at the bottom of the column, they are in square brackets, e.g.

Colin
Allan
[Alex]
Steve
[Jim]
John
[Pete]
Brian

Should become

Allan
Brian
Colin
John
Steve
[Alex]
[Jim]
[Pete]

It can be either VBA or some clever sort function.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If your data was in column A, I would make two separate sub list in B and C for the brackets and non brackets
in B1
=IF(NOT(ISERROR(AND(FIND("[",A1),FIND("]",A1)))),A1,"")
in C1
=IF(B1="",A1,"")
copy down

Then sort both lists, and append column B to the bottom of column C

Cheers

Dave

Author

Commented:
Thanks Dave

I was trying to avoid adding extra columns because there are all sorts of other things going on in the worksheet that would be messed up by adding columns and I was hoping that there was a way of resolving it  locally.

I did wonder about doing something like adding  zz before the [ doing a sort and then deleting the zz.
Would that or something along those lines work?

Can you give me some code that would do that please?

Thanks
this will work in situ

Cheers
Dave

Sub FindSort()
    Dim rng1 As Range, rng2 As Range, cel As Range, c As Range
    Dim FirstAddress As String
    Dim AppCalc As Long
    Dim Prefix As String

    Prefix = Application.WorksheetFunction.Rept("Z", 10)

    Set rng1 = Columns("A")


    With Application
        AppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set cel = rng1.Find("[*]", , xlValues, xlPart, xlByRows)
    If Not cel Is Nothing Then
        Set rng2 = cel
        FirstAddress = cel.Address
        Do
            Set cel = rng1.FindNext(cel)
            Set rng2 = Union(rng2, cel)
        Loop While FirstAddress <> cel.Address
    End If

    If Not rng2 Is Nothing Then
        For Each c In rng2
            c.Value = Prefix & c.Value
        Next
    End If
    rng1.Sort Key1:=Range("a1"), Order1:=xlAscending
    rng1.Replace Prefix, vbNullString
    With Application
        .ScreenUpdating = True
        .Calculation = AppCalc
    End With

End Sub

Open in new window

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Most Valuable Expert 2011
Top Expert 2011

Commented:
You could also create a custom list  that has A-Z and then [ at the end. You can then sort by this list and your [ data will be at the end.
Regards,
Rory

Author

Commented:
OK Dave that seems to do the trick thanks.

Just one thing, I should have said I would like to sort the complete row based on column A
Whats the best way to do that?


Rory
The data that’s in Column A is variable so I don’t know the contents of it up front.
How would you create a custom list  that has A-Z and then [ at the end and then sort by this list?
Thanks
Most Valuable Expert 2011
Top Expert 2011

Commented:
Which version of Excel are you using?

Author

Commented:
its 2007 i have but i would like it to work on 2003 as well if possible.
use this line for the sort

rng1.EntireRow.Sort Key1:=Range("a1"), Order1:=xlAscending

The code does check for both a [ and ] - if that is actually an issue

Cheers

Dave
Most Valuable Expert 2011
Top Expert 2011

Commented:
In 2003, it's Tools-Options-Custom Lists, then type:
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
[

into the box and press Add.

Then when sorting your data, choose Data-Sort, press Options... and change the first key sort order to your custom list.

In 2007, you add the custom list via the Excel Options.

Author

Commented:
Hi Dave
I am getting a Run-time error '1004'
Applicatiom-defined error on that line

Any thoughts?
to make sure I gave you the right mods :)

Sub FindSort()
    Dim rng1 As Range, rng2 As Range, cel As Range, c As Range
    Dim FirstAddress As String
    Dim AppCalc As Long
    Dim Prefix As String

    Prefix = Application.WorksheetFunction.Rept("Z", 10)

    Set rng1 = Columns("A")


    With Application
        AppCalc = .Calculation
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set cel = rng1.Find("[*]", , xlValues, xlPart, xlByRows)
    If Not cel Is Nothing Then
        Set rng2 = cel
        FirstAddress = cel.Address
        Do
            Set cel = rng1.FindNext(cel)
            Set rng2 = Union(rng2, cel)
        Loop While FirstAddress <> cel.Address
    End If

    If Not rng2 Is Nothing Then
        For Each c In rng2
            c.Value = Prefix & c.Value
        Next
    End If
    rng1.EntireRow.Sort Key1:=Range("a1"), Order1:=xlAscending
    rng1.Replace Prefix, vbNullString
    With Application
        .ScreenUpdating = True
        .Calculation = AppCalc
    End With

End Sub

Open in new window

Most Valuable Expert 2011
Top Expert 2011

Commented:
In fact, it seems you don't need the [ as the last entry in the custom list. Just specifying the alphabet will do it.

Author

Commented:
Hi Dave

Ok i have got that working on a demo sheet but in my main sheet i only want to do the sort on rows that come after a cell in A12 which has the range title "Subject"
Do i have to change this line
  Set rng1 = Columns("A")
to make it work for all rows after Range("Subject").offset(1,0)

Thanks

Rorya
I cant see where in Excel Options you set this up?
Can you point me in the right direction please?

Thanks
Most Valuable Expert 2011
Top Expert 2011
Commented:
In the Popular section, there's an Edit Custom Lists... button.

Author

Commented:
Not had a chance to implement this yet but this is still active
Thanks

Author

Commented:
Thanks for your help guys....
Got this working now.

Cheers

Gordon

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial