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


--
KarenScott
------------------------------------------------------------------------
KarenScott's Profile: http://www.excelforum.com/member.php...o&userid=35337
View this thread: http://www.excelforum.com/showthread...hreadid=551056

Postcode Sort?
 

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

Postcode Sort?
 

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

Postcode Sort?
 

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" wrote
in message ...

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

Postcode Sort?
 

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.

Postcode Sort?
 

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)

Postcode Sort?
 

Paul Lautman Wrote:
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.


--
KarenScott
------------------------------------------------------------------------
KarenScott's Profile: http://www.excelforum.com/member.php...o&userid=35337
View this thread: http://www.excelforum.com/showthread...hreadid=551056

Postcode Sort?
 

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