software

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 quot;thequot; 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 quot;thequot; by
entering

A2: quot;The Killingquot;
B2: =IF(LEFT(UPPER(A2),4)=quot;THE quot;,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 quot;thequot; and the word quot;aquot; or is that just asking
too much.

by the way, i apologize for all the killing.

thanks for your help in advanceAlso...quot;An Affair To Rememberquot;, quot;9 to 5quot;.

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

Jim Cone
San Francisco, USA
www.realezsites.com/bus/primitivesoftware
quot;m1rr0rshadequot; gt;
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 quot;thequot; 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 quot;thequot; by
entering

A2: quot;The Killingquot;
B2: =IF(LEFT(UPPER(A2),4)=quot;THE quot;,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 quot;thequot; and the word quot;aquot; or is that just asking
too much.

by the way, i apologize for all the killing.

thanks for your help in advanceOn Sat, 11 Mar 2006 11:40:23 -0800, m1rr0rshade
gt; wrote:

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

Here's a non-commercial solution.

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

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

=REGEX.SUBSTITUTE(A1,quot;^(A|The)\squot;,,,,FALSE,TRUE)

The Regular Expression quot;^(A|The)\squot; 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:

quot;^(A|The|An)\squot;

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

In your display column, enter the formula:

=INDEX(VSORT(rng,REGEX.SUBSTITUTE(rng,quot;^(A|The)\squot; ,,,,
FALSE,TRUE),1),ROWS($1:1) COUNTBLANK(rng))

and copy/drag down. This column will display the entries in quot;rngquot; 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
quot;rngquot; 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

quot;Ron Rosenfeldquot; wrote:

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

On Mon, 13 Mar 2006 18:30:28 -0800, m1rr0rshade
gt; wrote:

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

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