Postcode Sort?

K

KarenScott

Hi There

Could you please help? I have a small database which needs to be sorte
correctly by postcode but I can't get it to work. Eg the list goes G1
G11, G12 etc where it should go G1 G2 G3 etc.

Does anyone know of a sorting solution?

Thanks
Karen:
 
P

Paul Mathews

Hi Karen, if I understand correctly, you have a list of postal codes that are
a horizontal vector and you'd like them to be vertical. If that's right, you
can copy the list then, in cell G1, paste special and select the "Transpose"
option.
 
T

Toppers

Karen,
You can't without separating the text i.e "G", from the digits
as text fields will sort in the order defined in your posting.

Col A Col B
G 1
G 21
G 11
G 2

Sorted by A then B will get the desired result correct.

Concatenating A & B will get you back to the original codes.

Is this possible/practical?
 
B

Bob Phillips

You could extract the components then sort by these

E.g

=LEFT(A1),1
=MID(A1,2,2)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
P

Paul Lautman

KarenScott said:
Hi There

Could you please help? I have a small database which needs to be
sorted correctly by postcode but I can't get it to work. Eg the list
goes G1, G11, G12 etc where it should go G1 G2 G3 etc.

Does anyone know of a sorting solution?

Thanks
Karen:)
Click to expand...

Ahh, I know your problem. It is one that I first saw solved in an RPG
program written for an IBM System 36.

The trick was to "normalise" the postcodes into their official groupings of
Area, District, Sector, Sub-sector and then sort.

There are a few ways to tackle this. First of all, can I ask if in your case
it is allowable to split the code into its constituent parts in 4 separate
helper columns or would one helper column (with possibly a more complicated
formula) be better. In each case these helper columns would be in addition
to the standard post code column and would be there only for sorting
purposes.
 
P

Paul Lautman

Bob said:
You could extract the components then sort by these

E.g

=LEFT(A1),1
=MID(A1,2,2)
Click to expand...
That doesn't work for postcode with a 2 character Area part (e.g. SW1A 4AA)
 
K

KarenScott

Paul said:
Ahh, I know your problem. It is one that I first saw solved in an RPG
program written for an IBM System 36.

The trick was to "normalise" the postcodes into their officia
groupings of
Area, District, Sector, Sub-sector and then sort.

There are a few ways to tackle this. First of all, can I ask if in you
case
it is allowable to split the code into its constituent parts in
separate
helper columns or would one helper column (with possibly a mor
complicated
formula) be better. In each case these helper columns would be i
addition
to the standard post code column and would be there only for sorting
purposes.
Click to expand...

Hi There

I don't have a problem separating the postcode parts but I am not sur
how this work. I have full control of the database and so pleas
anything thing you suggest I'll try
 
J

Jim Cone

Top