sorting issues for movie titles

i have been cataloguing my movie collection as it grows and learning about
excel as i go.... my initial work around for movies beginning with "the" was
to enter them as follows

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe

that worked for a while but then i noticed when i added the move The Killing
and sorted by title i got

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe
Killing, The

when what i wanted was

Killer, The
Killer's Kiss
Killing, The
Killing Fields, The
Killing Zoe

i know this happens because excel is sorting the coma character after a-z.
so i found a suggestion to use a helper column to remove the word "the" by
entering

A2: "The Killing"
B2: =IF(LEFT(UPPER(A2),4)="THE ",MID(A2,5,200),A2)

i was happy until i started redoing the data entry and i noticed the
following complication.... i also want

A Night to Remember
A Real Young Girl
The Killing
The Killing Fields
Killing Zoe

to sort in the following order

The Killing
The Killing Fields
Killing Zoe
A Night to Remember
A Real Young Girl

is it possible to modify or expand what i enter in the helper column in
order to remove both the word "the" and the word "a" or is that just asking
too much.

by the way, i apologize for all the killing.

thanks for your help in advance

Also..."An Affair To Remember", "9 to 5".

A commercial application from yours truly...
http://www.officeletter.com/blink/specialsort.html

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"m1rr0rshade" <m1rr0rshade@discussions.microsoft.com>
wrote in message
i have been cataloguing my movie collection as it grows and learning about
excel as i go.... my initial work around for movies beginning with "the" was
to enter them as follows

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe

that worked for a while but then i noticed when i added the move The Killing
and sorted by title i got

Killer, The
Killer's Kiss
Killing Fields, The
Killing Zoe
Killing, The

when what i wanted was

Killer, The
Killer's Kiss
Killing, The
Killing Fields, The
Killing Zoe

i know this happens because excel is sorting the coma character after a-z.
so i found a suggestion to use a helper column to remove the word "the" by
entering

A2: "The Killing"
B2: =IF(LEFT(UPPER(A2),4)="THE ",MID(A2,5,200),A2)

i was happy until i started redoing the data entry and i noticed the
following complication.... i also want

A Night to Remember
A Real Young Girl
The Killing
The Killing Fields
Killing Zoe

to sort in the following order

The Killing
The Killing Fields
Killing Zoe
A Night to Remember
A Real Young Girl

is it possible to modify or expand what i enter in the helper column in
order to remove both the word "the" and the word "a" or is that just asking
too much.

by the way, i apologize for all the killing.

thanks for your help in advance

On Sat, 11 Mar 2006 11:40:23 -0800, m1rr0rshade
<m1rr0rshade@discussions.microsoft.com> wrote:

>i have been cataloguing my movie collection as it grows and learning about
>excel as i go.... my initial work around for movies beginning with "the" was
>to enter them as follows
>
>Killer, The
>Killer's Kiss
>Killing Fields, The
>Killing Zoe
>
>that worked for a while but then i noticed when i added the move The Killing
>and sorted by title i got
>
>Killer, The
>Killer's Kiss
>Killing Fields, The
>Killing Zoe
>Killing, The
>
>when what i wanted was
>
>Killer, The
>Killer's Kiss
>Killing, The
>Killing Fields, The
>Killing Zoe
>
>i know this happens because excel is sorting the coma character after a-z.
>so i found a suggestion to use a helper column to remove the word "the" by
>entering
>
>A2: "The Killing"
>B2: =IF(LEFT(UPPER(A2),4)="THE ",MID(A2,5,200),A2)
>
>i was happy until i started redoing the data entry and i noticed the
>following complication.... i also want
>
>A Night to Remember
>A Real Young Girl
>The Killing
>The Killing Fields
>Killing Zoe
>
>to sort in the following order
>
>The Killing
>The Killing Fields
>Killing Zoe
>A Night to Remember
>A Real Young Girl
>
>is it possible to modify or expand what i enter in the helper column in
>order to remove both the word "the" and the word "a" or is that just asking
>too much.
>
>by the way, i apologize for all the killing.
>
>thanks for your help in advance

Here's a non-commercial solution.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

To set up a helper column, where "the", "a", or any other First Word is
eliminated, you can use the formula:

=REGEX.SUBSTITUTE(A1,"^(A|The)\s",,,,FALSE,TRUE)

The Regular Expression "^(A|The)\s" is looking to match the pipe-separated
list of phrases that are within the parentheses, and replace it with nothing.

So you could, for example, expand it as desired:

"^(A|The|An)\s"

If you wanted to have a range that would dynamically sort, you could have a
data entry column named "rng" where you just enter the film names sequentially.
"rng" cannot refer to an entire column.

In your display column, enter the formula:

=INDEX(VSORT(rng,REGEX.SUBSTITUTE(rng,"^(A|The)\s",,,,
FALSE,TRUE),1),ROWS($1:1)+COUNTBLANK(rng))

and copy/drag down. This column will display the entries in "rng" in sorted
order.

This formula will give a #REF! error if you have more formula rows than
entries. This latter can be taken care of either in the formula itself, or by
using conditional formatting to check for the #REF! error and change the font
to the background color if it is present.

The dynamically sorted solution can take a long time depending on how large
"rng" is.
--ron

You can remove one problem by eliminating the use of commas. Since EXCEL
generally sorts using the ASCII table, the comma will certainly screw up the
sort. You can experiment with the ASCII table by using EXCEL.

In B1 enter 1
In B2 enter 2
Fill in 1,2,3 up to 128
Then in A1 enter the formula =CHAR(B1)
copy down.

This will display the logical sort parameters.

HTH John

thanks ron... i downloaded morefunc.xll and used the formula you suggested.
it does exactly what i was looking for... thanks

"Ron Rosenfeld" wrote:

> On Sat, 11 Mar 2006 11:40:23 -0800, m1rr0rshade
> <m1rr0rshade@discussions.microsoft.com> wrote:
>
> >i have been cataloguing my movie collection as it grows and learning about
> >excel as i go.... my initial work around for movies beginning with "the" was
> >to enter them as follows
> >
> >Killer, The
> >Killer's Kiss
> >Killing Fields, The
> >Killing Zoe
> >
> >that worked for a while but then i noticed when i added the move The Killing
> >and sorted by title i got
> >
> >Killer, The
> >Killer's Kiss
> >Killing Fields, The
> >Killing Zoe
> >Killing, The
> >
> >when what i wanted was
> >
> >Killer, The
> >Killer's Kiss
> >Killing, The
> >Killing Fields, The
> >Killing Zoe
> >
> >i know this happens because excel is sorting the coma character after a-z.
> >so i found a suggestion to use a helper column to remove the word "the" by
> >entering
> >
> >A2: "The Killing"
> >B2: =IF(LEFT(UPPER(A2),4)="THE ",MID(A2,5,200),A2)
> >
> >i was happy until i started redoing the data entry and i noticed the
> >following complication.... i also want
> >
> >A Night to Remember
> >A Real Young Girl
> >The Killing
> >The Killing Fields
> >Killing Zoe
> >
> >to sort in the following order
> >
> >The Killing
> >The Killing Fields
> >Killing Zoe
> >A Night to Remember
> >A Real Young Girl
> >
> >is it possible to modify or expand what i enter in the helper column in
> >order to remove both the word "the" and the word "a" or is that just asking
> >too much.
> >
> >by the way, i apologize for all the killing.
> >
> >thanks for your help in advance
>
> Here's a non-commercial solution.
>
> Download and install Longre's free morefunc.xll add-in from
> http://xcell05.free.fr
>
> To set up a helper column, where "the", "a", or any other First Word is
> eliminated, you can use the formula:
>
> =REGEX.SUBSTITUTE(A1,"^(A|The)\s",,,,FALSE,TRUE)
>
> The Regular Expression "^(A|The)\s" is looking to match the pipe-separated
> list of phrases that are within the parentheses, and replace it with nothing.
>
> So you could, for example, expand it as desired:
>
> "^(A|The|An)\s"
>
> If you wanted to have a range that would dynamically sort, you could have a
> data entry column named "rng" where you just enter the film names sequentially.
> "rng" cannot refer to an entire column.
>
> In your display column, enter the formula:
>
> =INDEX(VSORT(rng,REGEX.SUBSTITUTE(rng,"^(A|The)\s",,,,
> FALSE,TRUE),1),ROWS($1:1)+COUNTBLANK(rng))
>
> and copy/drag down. This column will display the entries in "rng" in sorted
> order.
>
> This formula will give a #REF! error if you have more formula rows than
> entries. This latter can be taken care of either in the formula itself, or by
> using conditional formatting to check for the #REF! error and change the font
> to the background color if it is present.
>
> The dynamically sorted solution can take a long time depending on how large
> "rng" is.
> --ron
>

On Mon, 13 Mar 2006 18:30:28 -0800, m1rr0rshade
<m1rr0rshade@discussions.microsoft.com> wrote:

>thanks ron... i downloaded morefunc.xll and used the formula you suggested.
>it does exactly what i was looking for... thanks

You're welcome. Glad to help. Thanks for the feedback.
--ron