Postcode Sort?

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

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.

"KarenScott" wrote:

>
> 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
>
>
> --
> KarenScott
> ------------------------------------------------------------------------
> KarenScott's Profile: http://www.excelforum.com/member.php...o&userid=35337
> View this thread: http://www.excelforum.com/showthread...hreadid=551056
>
>

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?

"KarenScott" wrote:

>
> 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
>
>
> --
> KarenScott
> ------------------------------------------------------------------------
> KarenScott's Profile: http://www.excelforum.com/member.php...o&userid=35337
> View this thread: http://www.excelforum.com/showthread...hreadid=551056
>
>

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)

"KarenScott" <KarenScott.29apg3_1150126209.3826@excelforum-nospam.com> wrote
in message news:KarenScott.29apg3_1150126209.3826@excelforum-nospam.com...
>
> 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
>
>
> --
> KarenScott
> ------------------------------------------------------------------------
> KarenScott's Profile:
http://www.excelforum.com/member.php...o&userid=35337
> View this thread: http://www.excelforum.com/showthread...hreadid=551056
>

KarenScott wrote:
> 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.

Bob Phillips wrote:
> 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)

Originally Posted by Paul Lautman

KarenScott wrote:
> 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.

Hi There

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.

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.
--
KarenScott