This is for a Movie Have/Want list using Google Sheets.
Column A is the movie Title. I would like to be able to sort the movie titles alphabetically but ignore when a movie title begins with "The", "An" or "A". For example: "The Abyss" would be filed under 'A' for "Abyss" but would still display as "The Abyss".
I would also like it to ignore a few other words that I can continue to enter as needed. For example: "Marvel's"... So that "Marvel's Iron Man" would be filed under 'I' for "Iron Man" but would still display as "Marvel's Iron Man".
I know how to do it with this in a new column:
=IF(LEFT(A2,2)="A ",RIGHT(A2,LEN(A2)-2),IF(LEFT(A2,3)= "An ",RIGHT(A2,LEN(A2)-3),IF(LEFT(A2,4)="The ",RIGHT(A2, LEN(A2)-4),A2)))
Then I just sort by that column and hide it. Not really a very good solution but it works. However, with the additional things I want to do below, I'm guessing I'll need a script.
I have all the movies we "Have" and all the movies we still "Want" in the same list. I have it this way because when we get a movie from our wanted list, all we have to do is change the entry in the "Ownership" column from "Want" to "Have".
I would like to make it so that the movies are sorted by default to first show movies we "Have" alphabetically and then movies we "Want" alphabetically but of course, it would still be nice not to be locked into that sorting default so that friends and family can sort as they like (by year, etc.) when browsing our catalog.
I have two different tabs. The first tab has the movie title along with which edition it is, what format it is etc. So sometimes there may be multiple entries for the same movie. For example:
- "The Abyss (Theatrical)" "1989" "DVD"
- "The Abyss (Special Edition)" "1989" "DVD"
- "The Abyss (Special Edition)" "1989" "Blu-ray"
- "Alfie" "1966" "DVD"
- "Alfie" "2004" "DVD"
I have a second tab that is simply a far-less info-filled count of all of the separate movies titles we have NOT counting duplicate movies. For Example: (Notice that The Abyss (Special Edition) is not listed twice like it is above.)
- "The Abyss (Theatrical)" "1989"
- "The Abyss (Special Edition)" "1989"
- "Alfie" "1966"
- "Alfie" "2004"
I would like to be able to pull all the movie titles and years (two different columns, perhaps even a third column) from the first tab and have them placed in the second tab automatically but skipping duplicates.
To complicate things even further, I would love to also be able to pull the color formatting of the titles from the first tab into the second tab as well if at all possible. I use color to distinguish between different versions of a film like remakes, etc.
Alternative to Problem 3:
I just thought of this alternative. The second tab is for a more simplified list but is mostly for counting purposes. It adds up all of our Haves and Wants and gives totals for each and an overall total. If there were a way to instead count the "Have" and "Want" entries but then compare them to the title and year columns so not to count duplicates, that might be fine as well. Perhaps not my preferred way to do it but if it makes the solution more possible, I'll take what I can get.
The first problem by itself is not that bad but with all the additional things I'm trying to accomplish, it's way beyond my ability.