-2

This is for a Movie Have/Want list using Google Sheets.

Problem 1:

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.

Problem 2:

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.

Problem 3:

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.

closed as too broad by Rubén, ale, user79865, Hydra, codingbadger Sep 18 '15 at 9:46

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    -1: Too broad. Please consider to split your question in several questions, perhaps, one by each problem. Also, I think that you could find helpful the following post in Meta: How do I write a great question? – Rubén Sep 8 '15 at 11:23
  • 1
    Yes, please do split this into individual questions. That way answerers can focus on one problem and people can vote for complete solutions. – ale Sep 8 '15 at 17:56
  • I put it into one question since I thought all of the solutions might need to be integrated into one single script. But I will try putting it into separate questions. Thank you. – Computer Candy Sep 8 '15 at 20:22
2

Database Software
I think Google Sheets can do this but do consider whether to use proper database software (Google for "database software"). Those will handle tables of data with more/easier high-level control. They won't have cell formulas that can get inconsistent between rows, or cells accidentally added outside of a desired range.

There are many choices of database programs and services. It sounds like a "flat file" manager would suffice, but a "relational database" is fine, too. See MySQL, MS Access, Google Fusion Tables, Zoho, etc. Some of these run as network services, and it's possible to set up MySQL running on an Amazon server or use Google's Cloud SQL.

Movie Collection Software
Potentially even better is to use polished movie collection software (Google for "movie collection software"). Those will have movie-specific features like automatically retrieving cover art and other info from IMDB, and auto-competing movie names when you type them in.

But using Google Sheets, you'll learn interesting skills that will be useful for other spreadsheets.

How to sort by simplified titles
You're on the right track by creating another column that has the simplified titles. But using lots of IF() expressions will get hard to manage. Better to use the regular expression features in Sheets functions or JavaScript regular expressions in an Apps script like this:

/**
 * @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet.
 */

/**
 * Given an input value or range of values returns a value or a range less any prefix
 * word "A" | "An" | "The" and less leading and trailing white space.
 */
function STRIP(input) {
  if (input instanceof Array) {
    // Recurse to process an array.
    return input.map(STRIP);
  } else if (typeof input == 'string') {
    // Process as a single value.
    var re = /^((A|An|The)\s+)?(.*)$/i; // "i" means case insensitive
    return input.trim().replace(re, '$3');
  }
  // Return the input unchanged.
  return input;
}

How to sort by title in two groups (owned, wanted)
Just ask Sheets to sort by the owned/wanted column and subsort by the simplified title column -- the first and second sort keys.

Note that if you have a function like =STRIP(A2:A1000) in cell B2 to set the range of values B2:B1000, if you then Sort the range, that may move the cell containing the formula so the formula no longer sets the desired cell values. You can work around that by putting the formula =STRIP(A1:A1000) in the table's title row, B1, and configuring sort with "Data has a header row". A1 contains "The Full Title", B1 will show "Full Title".

How to count groups
Try using the COUNTIFS() function for that.

How to filter
Check out the Filter and Filter View features and spreadsheet add-ons like EZ Query.

How to copy selected rows and their colors
You can write an Apps Script function to do this. See the Apps Script docs and tutorials for examples of writing a function that can run from a menu command and loop over selected cells or a named range. It can get properties like color from those rows as well as the text contents, decide which rows to copy, then store them in your second sheet.

See the Spreadsheet Service reference, e.g. how to get a cell's background color.

  • Thank you very much for your reply. I had considered a database program but we've already put a lot into it and this is easy for our friends and family (that we share our movies with) to easily browse online from anywhere. – Computer Candy Sep 8 '15 at 8:10
  • I have been looking a lot at scripts. Unfortunately, I'm running into a lot problems. I don't understand it well enough to be able to write a script even from examples since there aren't really any examples that come too close to everything I want to do. I've had some success in some very basic scripts but nothing even close enough to encompass it all. – Computer Candy Sep 8 '15 at 8:11
  • I added Apps Script code to strip prefixes like A|An|The, more background notes, alternatives, and suggestions. – Jerry101 Sep 8 '15 at 23:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.