Tips or tricks required to sort UK postcodes

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
A bit of a shot in the dark but I'm trying to sort records by the first part of a UK postcode and wonder if anyone has any shortcuts. I'm trying to sort them using VBA as it is a final part of a procedure.

This is an example of what I am looking for

AB1
AB2
AB3
AB10
AB12
AB20
B1
B2
CH1
CH2
CH11

I can seperate the numeric from the alpha part of the postcode but then require two columns to sort the data. Is it possible to sort the data in a single column.

thanks

Matt
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Dexter

Board Regular
Joined
Apr 3, 2002
Messages
71
I'm not aware of any way to sort other than your idea of creating a separate column in which to extract out the sort characters.
 

chef

Well-known Member
Joined
Jul 10, 2002
Messages
568
Office Version
  1. 365
  2. 2016
matt..

say your postcode is in a1 etc

use the =left formula..

eg =left(a1,2,etc will give you the postcode

will display the postcode..then you can cancatenate..not entirely sure what you are trying to do but =left will def help

regards
chef
 

Jak

Well-known Member
Joined
Apr 5, 2002
Messages
825
Hi Matt

Can't help with VB but just had a thought. You could create a custom list with your raw data and then use the sort function to sort ascending etc.

As I said, just a thought you would need to experiment.

[Edit]

Tried this out after post. Excel does values, text, logical values, error values and then blanks. I tried creating a custom list in line with how Excel sorts but it got complicated.
This message was edited by Jak on 2002-10-22 16:38
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

You could sort on the column C value shown below...
Book1
ABCDEFGHIJ
1AB12AB01
2AB22AB02
3AB32AB03
4AB102AB10
5AB122AB12
6AB202AB20
7B11B001
8B21B002
9CH12CH01
10CH22CH02
11CH112CH11
12
Sheet3


Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-10-22 17:41
 

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
Thanks for your help guys, will be trying the suggestions right away.
 

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
I'm using the following code as aided by Mark
to manipulate uk postcodes and then to sort alpha and then numerically. The code works fine on smaller sheets (less than 100 records) and when I step through the code but starts to struggle after 150 records and locks up. Does anyone have any suggestions to make this more efficient?

Thanks

Matt

Sub formulatest()

Dim myrange As Range
Dim lastrow As Integer

Application.ScreenUpdating = False

Columns("K:M").Select
Selection.Insert Shift:=xlToRight


lastrow = Range("A65536").End(xlUp).Row

Set myrange = Range("K2:K" & lastrow)


For Each cell In myrange

cell.Formula = "=TRIM(LEFT(" & cell.Offset(0, -1).Address & "" & ",4))"

cell.Offset(0, 1).FormulaArray = "=MAX(ISERROR(MID(" & cell.Address & ",ROW(INDIRECT(""1:""&LEN(" & cell.Address & "))),1)+0)*ROW(INDIRECT(""1:""&LEN(" & cell.Address & "))))"

cell.Offset(0, 2).FormulaArray = "=LEFT(" & cell.Address & "," & cell.Offset(0, 1).Address & ")&TEXT(RIGHT(" & cell.Address & ",LEN(" & cell.Address & ")-" & cell.Offset(0, 1).Address & "),REPT(0,MAX(LEN($K$2:$K$" & lastrow & "))-" & cell.Offset(0, 1).Address & "))"

Next

Cells.Select
Range("J1").Activate
Selection.Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("K:M").Select
Selection.Delete Shift:=xlToLeft

Application.ScreenUpdating = True

End Sub
This message was edited by Matt on 2002-10-23 10:46
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top