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.
Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:
=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))
You don't need transpose
, because join
works equally well with one-dimensional arrays of either direction: vertical or horizontal.
That said, I find a solution using filter
slightly more readable:
=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))
Best Answer
An aging, but good, question. You'll need a bit of understanding of how Sheets works "under the hood" to achieve this. There is no data type in Sheets for the "time range" type entries you listed out. You have a couple options from here
12/16/2018 3 PM
instead of a range, with the understanding that 3 PM represents 3 to 4. Note that date-times in Sheets are just numbers, and you may set them to appear with whatever custom formatting you choose; 3 PM, 15:00:00, 20181216 3:00, etc.You would achieve this by adding (+) rather than concatenating (&) the time. Times are also stored in units of days(!), so with 3 PM being 15 hours into the day, it's stored as 15⁄24 of a day; literally 0.625 days. Rest assured that Sheets will render it according to its defaults or your selected custom format. To grab the date in E1 and output 3 PM of that day, you use a formula resembling
=E1+(15/24)
=TEXT(E1,"mm/dd/yyyy") & " 3-4"