Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

OK - I pasted that formula into column B. Now do I just type The and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

Thanks - that took care of the one's with "A." But I have one last question:
Now the new titles in column B that started with "The," start with "he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last question:
Now the new titles in column B that started with "The," start with "he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last question:
Now the new titles in column B that started with "The," start with "he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A" etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND(" ",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A". Is it
possible to sort the list alphabetically (like in a library) without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well


=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND (" ",A2)-1),"")),A2)


copy down as long as needed, select both columns and sort ascending on the
help column

I am assuming that the first film title is in A2


--

Regards,

Peo Sjoblom


"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda

Just an unfortunate line break:

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),{"The";"A"},0)),
TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)-1),"")),A2)

(all in one cell)

Peo Sjoblom wrote:

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well

=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND (" ",A2)-1),"")),A2)

copy down as long as needed, select both columns and sort ascending on the
help column

I am assuming that the first film title is in A2

--

Regards,

Peo Sjoblom

"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda


--

Dave Peterson

Thanks! That worked!

"Dave Peterson" wrote:

Just an unfortunate line break:

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),{"The";"A"},0)),
TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)-1),"")),A2)

(all in one cell)

Peo Sjoblom wrote:

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well

=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND (" ",A2)-1),"")),A2)

copy down as long as needed, select both columns and sort ascending on the
help column

I am assuming that the first film title is in A2

--

Regards,

Peo Sjoblom

"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and "A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and "A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so, how?

Thanks,
Amanda


--

Dave Peterson

Gee! What happened?


Peo


"Dave Peterson" wrote in message
...
Just an unfortunate line break:

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),{"The";"A"},0)),
TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)-1),"")),A2)

(all in one cell)

Peo Sjoblom wrote:

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well

=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND ("
",A2)-1),"")),A2)

copy down as long as needed, select both columns and sort ascending on
the
help column

I am assuming that the first film title is in A2

--

Regards,

Peo Sjoblom

"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type
The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and
"A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and
"A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so,
how?

Thanks,
Amanda


--

Dave Peterson

The space at the end of your first line was turned into a linefeed.

I wasn't trying to take credit for the formula (this time! <vbg.)

Peo Sjoblom wrote:

Gee! What happened?

Peo

"Dave Peterson" wrote in message
...
Just an unfortunate line break:

=IF(ISNUMBER(MATCH(LEFT(A2,FIND(" ",A2)-1),{"The";"A"},0)),
TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(" ",A2)-1),"")),A2)

(all in one cell)

Peo Sjoblom wrote:

If you just want to avoid A and The, in a help column (B or whatever) put

First of all, Tevuna's solution works fine. You must have applied his
solution incorrectly. This is just another solution with the words
hardcoded, if you need to
avoid films that start with Of you can just add that word as well

=IF(ISNUMBER(MATCH(LEFT(A2,FIND("
",A2)-1),{"The";"A"},0)),TRIM(SUBSTITUTE(A2,LEFT(A2,FIND ("
",A2)-1),"")),A2)

copy down as long as needed, select both columns and sort ascending on
the
help column

I am assuming that the first film title is in A2

--

Regards,

Peo Sjoblom

"Amandar097" wrote in message
...
OK - thanks so much for your help and patience with all my questions :)

"Tevuna" wrote:

Interesting. I havn't a clue what could be wrong at yours.
Wait till tomorrow morning when the gurus awaken.

"Amandar097" wrote:

Thanks - that took care of the one's with "A." But I have one last
question:
Now the new titles in column B that started with "The," start with
"he." Do
you know why that would happen?

Thanks,
Amanda

"Tevuna" wrote:

1) Type each word on its own row:

C
1 the
2 A
3 of

2) Paste the formula down column B
3) Resort column B


"Amandar097" wrote:

OK - I pasted that formula into column B. Now do I just type
The
and A into
column C with a space or a comma (The A) or (The, A)?

After that, do I resort column A or column B?

Thanks,
Amanda

"Tevuna" wrote:

Assuming your titles are in column A
1) In column C enter all insignificant words like "the" and
"A"
etc.
2) Paste this in column B
=IF(ISERROR(VLOOKUP(LEFT(A1,FIND("
",A1)-1),C:C,1,FALSE)),A1,MID(A1,FIND("
",A1)+1,LEN(A1)))

"Amandar097" wrote:

Hello,

I have a list of movie titles. Some start with "The" and
"A".
Is it
possible to sort the list alphabetically (like in a library)
without using
those words as the primary words by which to sort? If so,
how?

Thanks,
Amanda


--

Dave Peterson

--

Dave Peterson

I wasn't trying to take credit for the formula (this time! <vbg.)


You got it nevertheless
Since you are a fellow Swede (at least somewhat) I won't be making any fuss
<vbg

Peo

Man, oh, man.

First, I have the Candian Quality Council (CQC) on my case.

Now, I have to watch out for the SIS (Swedish Integrity Society), too.

====
Keep an eye out (ouch!). You may see that formula posted (without attribution
after a few times!).



Peo Sjoblom wrote:

I wasn't trying to take credit for the formula (this time! <vbg.)

You got it nevertheless
Since you are a fellow Swede (at least somewhat) I won't be making any fuss
<vbg

Peo

--

Dave Peterson

First, I have the Candian Quality Council (CQC) on my case.


So you are a candy tester, big deal.


<g

That was one of Dave's rare intentional misspellings. <g

Maybe he thinks there are too many "ehs" in Canadian.

Peo Sjoblom wrote:
First, I have the Candian Quality Council (CQC) on my case.

So you are a candy tester, big deal.
<g


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

The original mistake wasn't on purpose. The subsequent have been on purpose
(mostley).

<vbg



Debra Dalgleish wrote:

That was one of Dave's rare intentional misspellings. <g

Maybe he thinks there are too many "ehs" in Canadian.

Peo Sjoblom wrote:
First, I have the Candian Quality Council (CQC) on my case.

So you are a candy tester, big deal.
<g

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

--

Dave Peterson