Google-sheets – Simultaneously sort multiple ranges on a single sheet in varying orders

Managing expectations

Scripts cannot intercept a user's click on column name. This action will always invoke built-in search. So, if you want users to sort by clicking a column, you need a separate column for sorting.

With a separate column

You don't need a complex formula with IFs to remove articles or other words: a regular expression can be used to handle all variants at once:

= regexreplace(A1, "(?i)^(a|an|the) ", "") 

(case-insensitive replacement), or

= regexreplace(lower(A1), "^(a|an|the) ", "") 

(also converting to lowercase, so that the sorting is case-insensitive).

This is easily extended to other words: (a|an|the|Marvel's) and so on.

With a script, for formula-less sheets

But if you really don't want another column, here's a script. What it does:

• Adds a "Sort > by Movie Title" command to the menu (when the spreadsheet is opened).
• When invoked with this command, sorts entire sheet A>Z by the first column, ignoring beginning a, an, the and also ignoring case.
• Does not sort by any other column (adding year, etc as a secondary criterion is up to you).

As a side effect, this script will remove any formulas from the sheet, replacing them with the current values in those cells. Which is unacceptable, unless you don't use formulas.

function sortByTitle() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange(); 
  var values = range.getValues();
  values.sort(function(a,b) {return (trimmed(a[0]) < trimmed(b[0]) ? -1 : 1);});
  range.setValues(values);
}

function trimmed(str) {
  return str.toLowerCase().replace(/^(a|an|the) /, ''); 
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Sort", [{name: "by Movie Title",  functionName: "sortByTitle"}]);
}

With a script, preserving formulas

This is a hybrid approach: it adds a new column filled with trimmed titles, invokes the method sort of the Range class, then clears the column. As a result, formulas are preserved (if your formulas make relative references to cells in other rows, sorting will still mess things up, but this is to be expected).

Here is the new sortByTitle function; the functions trimmed and onOpen remain as above.

function sortByTitle() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var height = sheet.getDataRange().getHeight();
  var width = sheet.getDataRange().getWidth();
  var titles = sheet.getRange(1, 1, height, 1).getValues();
  trimmedTitles = [];
  for (var i=0; i<titles.length; i++) {
    trimmedTitles.push([trimmed(titles[i][0])]);
  }
  var newColumn = sheet.getRange(1, width+1, height, 1);
  newColumn.setValues(trimmedTitles);
  sheet.getRange(1, 1, height, width+1).sort(width+1);   // see comment below
  newColumn.clear();
}

If your spreadsheet contains a header row (which is likely), replace the line with the comment with

  sheet.getRange(2, 1, height-1, width+1).sort(width+1);

so that the top row is excluded from sorting.

You don't need a script: the Filter command (which is different from filters and filter views) does this easily.

Suppose your master sheet is named Master and the closing date is in column B. Then in Sheet "Jan" you would enter

=filter(Master!A:Z, month(Master!B:B) = 1)

Here A:Z are the columns of Master sheet (of course there may be more, you can use A:ZZ, etc). The condition that month of the date in column B is equal to 1 means it's January.

In the sheet named "Feb" you would put

=filter(Master!A:Z, month(Master!B:B) = 2)

and so on. The edits to Master sheet will be immediately reflected in the month sheets.