sorting issues for movie titles

[Guest]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Ron Rosenfeld]

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

 

[John Eppley]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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