Postcode Sort?

[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:

 

[Guest]

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.

 

[Guest]

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?

 

[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)

 

[Paul Lautman]

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

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.

 

[Paul Lautman]

You could extract the components then sort by these

E.g

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

That doesn't work for postcode with a 2 character Area part (e.g. SW1A 4AA)

 

[KarenScott]

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.

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

 

[Jim Cone]

You could try a commercial alternative from yours truly...
The Excel add-in "Special Sort" should do what you want.
More info at...
http://www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"KarenScott"
wrote in message
Hi There
-snip-
I don't have a problem separating the postcode parts but I am not sure
how this work. I have full control of the database and so please
anything thing you suggest I'll try.