Pulling most-frequently used names from across Google sheets and ranking them on a master sheet

Question

I have created a spreadsheet with a "master" sheet and several individual sheets. On each individual sheet I hope to have each one of my colleagues input names. The master sheet should then be able to rank the most frequently used names. In short, the master sheet should show which names were mentioned most frequently in each of the categories: men, women, and alternate, and show how many times each name was mentioned.

Here is an example of what I am looking to do. There is a master sheet meant to collate the rankings and then several individual sheets where each person inputs their list.

Answer 1

Try the following script:

var WOMEN_COL_IDX=1; // WOMEN are listed in column 1 (column B)
var MEN_COL_IDX=2; // MEN are listed in column 2 (column C)
var ALT_COL_IDX=3; // ALTERNATES are listed in column 3 (column D)

function countVotes(what) {
  var colIdx;
  switch (what.toUpperCase()) {
    case "WOMEN": colIdx = WOMEN_COL_IDX; break;
    case "MEN": colIdx = MEN_COL_IDX; break;
    case "ALTERNATES": colIdx = ALT_COL_IDX; break;
    default: throw new Error("Unsupported argument: " + what + " - specify MEN, WOMEN or ALTERNATES." );
  }
  var ranges = Array.prototype.slice.call(arguments, 1); // All arguments except the first to this function are ranges to count
  // Collect votes in an object literal
  var votes = {};
  for (var i = 0; i < ranges.length; i++) {
    countRange(ranges[i], colIdx, votes);
  }
  // Transfer votes object to an array
  var result = [];
  for (var name in votes) {
    if (!votes.hasOwnProperty(name)) {
      continue;
    }
    result.push([name, votes[name]]);
  }
  // Now sort the results by votes, descending order
  result.sort(function(a, b) {
    return b[1] - a[1];
  });
  return result.slice(0, 7); // Return only 8 first rows
}

function countRange(data, colIdx, votes) {
  for (var i = 0; i < data.length; i++) { // Iterate rows on this sheet
    var row = data[i];
    var vote = row[colIdx];
    if (vote == "") {
      continue; // Skip blank cells
    }
    if (votes[vote]) {
      votes[vote] = votes[vote] + 1;
    } else {
      votes[vote] = 1;
    }
  }
}

The script returns a list of names and their votes, sorted by votes descending. Only the 8 most popular names are returned.

To use the script, you must first install it: In your spreadsheet, click Tools -> Script editor, and paste the script. From the drop-down menu in the Script Editor Toolbar, select countVotes, and click the Run button (you only need to do this once). This will give you an authorization dialog, in which you must accept.

In the cell where you want to display the result of the script (in your example spreadsheet that is B2 in the Master sheet), enter the following formula:

=countVotes("WOMEN", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)

This means: Count the names in the WOMEN column in the ranges A2:D on everyone's sheet.

Repeat this formula for the other results, so in D2 enter

=countVotes("MEN", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)

and in F2 enter

=countVotes("ALTERNATES", Paul!A2:D; John!A2:D; David!A2:D; Sally!A2:D)

I have set up an example spreadsheet to demonstrate the script. Feel free to copy it to your own Google Drive for further experimentation.