Help to sort road addresses - Complex Sort Scenario

[mazher]

Hi All Excel Gurus,

I have address in one column as follows

1 ABC Road
1a ABC Road
10 ABC Road
11 ABC Road
11a ABC Road
2 ABC Road
3 ABC Road
6 ABC Road
7 ABC Road
52 XYZ Road
The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3, 35 BCD Road
51 BCD Road
21 ABC Road
Flat 1 23 ABC Road
5 Court PQR Road
2 Court PQR Road
7 Court PQR Road
2a Court PQR Road


I need them sorted in the road order as follows
1 ABC Road
1a ABC Road
2 ABC Road
3 ABC Road
6 ABC Road
7 ABC Road
10 ABC Road
11 ABC Road
11a ABC Road
21 ABC Road
Flat 1 23 ABC Road
The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3 35 BCD Road
51 BCD Road
2 Court PQR Road
2a Court PQR Road
5 Court PQR Road
7 Court PQR Road
52 XYZ Road


Please can some one help me either with the formula approach or with VBA.

I will be extremely thankful for that, as its driving me crazy.

 

[Akuini]

Try this:
The code use col C & D as temporary helper column (they're deleted in the end of the code), you may change that.

[FONT=lucida console][color=Royalblue]Sub[/color] a1086751a()
[i][color=seagreen]'https://www.mrexcel.com/forum/excel-questions/1086751-help-sort-road-addresses-complex-sort-scenario.html[/color][/i]
[color=Royalblue]Dim[/color] i [color=Royalblue]As[/color] [color=Royalblue]Long[/color], j [color=Royalblue]As[/color] [color=Royalblue]Long[/color], n [color=Royalblue]As[/color] [color=Royalblue]Long[/color]
[color=Royalblue]Dim[/color] tx [color=Royalblue]As[/color] [color=Royalblue]String[/color]
[color=Royalblue]Dim[/color] va, vb, x, q

Application.ScreenUpdating = [color=Royalblue]False[/color]
n = Range([color=brown]"A"[/color] & Rows.count).[color=Royalblue]End[/color](xlUp).Row
va = Range([color=brown]"A1:A"[/color] & n)
[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]1[/color])
[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
x = Split(va(i, [color=crimson]1[/color]), [color=brown]" "[/color])
vb(i, [color=crimson]1[/color]) = x(UBound(x) - [color=crimson]1[/color])

[color=Royalblue]Next[/color]

Range([color=brown]"C1"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color]) = vb
[color=Royalblue]ReDim[/color] vb([color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color]), [color=crimson]1[/color] [color=Royalblue]To[/color] [color=crimson]1[/color])

[color=Royalblue]For[/color] i = [color=crimson]1[/color] [color=Royalblue]To[/color] UBound(va, [color=crimson]1[/color])
z = va(i, [color=crimson]1[/color])

[color=Royalblue]If[/color] [color=Royalblue]Not[/color] IsNumeric(Left(z, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
    vb(i, [color=crimson]1[/color]) = z
    [color=Royalblue]Else[/color]
        q = Split(z, [color=brown]" "[/color])([color=crimson]0[/color])
        [color=Royalblue]If[/color] IsNumeric(Right(q, [color=crimson]1[/color])) [color=Royalblue]Then[/color]
            vb(i, [color=crimson]1[/color]) = q
            [color=Royalblue]Else[/color]
            vb(i, [color=crimson]1[/color]) = Left(q, Len(q) - [color=crimson]1[/color])
        [color=Royalblue]End[/color] [color=Royalblue]If[/color]
[color=Royalblue]End[/color] [color=Royalblue]If[/color]

[color=Royalblue]Next[/color]

Range([color=brown]"D1"[/color]).Resize(UBound(vb, [color=crimson]1[/color]), [color=crimson]1[/color]) = vb
Range([color=brown]"A1:D"[/color] & n).Sort Key1:=Range([color=brown]"C1"[/color]), order1:=xlAscending, Key2:=Range([color=brown]"D1"[/color]), order2:=xlAscending, Header:=xlNo
Range([color=brown]"C1:D"[/color] & n).ClearContents
Application.ScreenUpdating = [color=Royalblue]True[/color]
[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

The result is a bit different from yours, check the yellow area:

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

A​

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

1​

[/td][td]1 ABC Road[/td][/tr]

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

2​

[/td][td]1a ABC Road[/td][/tr]

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

3​

[/td][td]2 ABC Road[/td][/tr]

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

4​

[/td][td]3 ABC Road[/td][/tr]

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

5​

[/td][td]6 ABC Road[/td][/tr]

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

6​

[/td][td]7 ABC Road[/td][/tr]

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

7​

[/td][td]10 ABC Road[/td][/tr]

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

8​

[/td][td]11 ABC Road[/td][/tr]

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

9​

[/td][td]11a ABC Road[/td][/tr]

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

10​

[/td][td]21 ABC Road[/td][/tr]

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

11​

[/td][td]Flat 1 23 ABC Road[/td][/tr]

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

12​

[/td][td=bgcolor:#FFFF00]51 BCD Road[/td][/tr]

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

13​

[/td][td=bgcolor:#FFFF00]Flat 3, 35 BCD Road[/td][/tr]

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

14​

[/td][td=bgcolor:#FFFF00]The Mansion , 8 BCD Road[/td][/tr]

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

15​

[/td][td=bgcolor:#FFFF00]The Quadrant , 8a BCD Road[/td][/tr]

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

16​

[/td][td]2 Court PQR Road[/td][/tr]

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

17​

[/td][td]2a Court PQR Road[/td][/tr]

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

18​

[/td][td]5 Court PQR Road[/td][/tr]

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

19​

[/td][td]7 Court PQR Road[/td][/tr]

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

20​

[/td][td]52 XYZ Road[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[mazher]

Its giving me complie error and I need the result like I have posted in my sample

The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3 35 BCD Road
51 BCD Road

 

[Akuini]

Do you mean using your example above gave you compile error? or you were using actual data?

 

[mazher]

Thanks for your time and help , I tried running that again on the sample data and no compile error.

I need the yellow highlighed cells in the same way

The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3 35 BCD Road
51 BCD Road

I have lot of entries in that format

The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3, 35 BCD Road

which will take for ever to sort them in the correct order road order.

 

[Akuini]

If the number is in the middle it is rather tricky because I don't know the right "pattern" to manipulate.
Is it always true that if the number is in the middle then there must be a comma before it? And there won't be more than 1 comma in the address.

 

[mazher]

Thanks again, I have checked my data that wherever there is comma , there is only one comma,

But I don't know how the handle these

The Mansion , 8 BCD Road
The Quadrant , 8a BCD Road
Flat 3, 35 BCD Road

Any way I am extremely thankful once again for your time and help,

I will wait might be some VBA expert will notice my request.

Thanks again Ajuini.

 

[Akuini]

You didn't answer this question:
Is it always true that if the number is in the middle then there must be a comma before it?

 

[mazher]

Here is the sample with all scenarios
Thomas Cottage, 271A Totteridge Road
Flat 1, 145 Bowerdean Road
Flat B, 21 Totteridge Lane
Heartoak House, 21a Totteridge Lane
1 Windrush Court, Windrush Drive
3 Denewood, Totteridge Road
7 Conway House, Hicks Farm Rise
25 Chartridge House, Windrush Drive
8 Denewood, Totteridge Road
10 Denewood, Totteridge Road
20 Leas Close, High Wycombe
21 Leas Close, High Wycombe
1 Ely House, Leas Close
1 Hereford House, Leas Close
2 York House, Leas Close


I need it sorted it like this way

Flat 1, 145 Bowerdean Road
Flat B, 21 Totteridge Lane
Heartoak House, 21a Totteridge Lane
3 Denewood, Totteridge Road
8 Denewood, Totteridge Road
10 Denewood, Totteridge Road
Thomas Cottage, 271A Totteridge Road
7 Conway House, Hicks Farm Rise
1 Ely House, Leas Close
1 Hereford House, Leas Close
2 York House, Leas Close
20 Leas Close
21 Leas Close
25 Chartridge House, Windrush Drive
1 Windrush Court, Windrush Drive

Hope this helps

 

[Akuini]

Hm, it's more complex than I thought.
I don't understand the criteria for the sort.
Why is Totteridge Road above Hicks Farm Rise?
Thomas Cottage, 271A Totteridge Road
7 Conway House, Hicks Farm Rise