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.
Best Answer
Try the following script:
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 theMaster
sheet), enter the following formula:This means: Count the names in the
WOMEN
column in the rangesA2:D
on everyone's sheet.Repeat this formula for the other results, so in
D2
enterand in
F2
enterI have set up an example spreadsheet to demonstrate the script. Feel free to copy it to your own Google Drive for further experimentation.