Often, we find ourselves in a group meeting where we have to come up with new ideas and then choose the best one. For example…
1) A speaker is coming to talk to a group, but only has time for the best 5 questions from the group.
2) A team of journalists gives story ideas for next week’s magazine. Or a team of comedy writers throws out sketch ideas for next week’s show.
3) A group of friends comes up with ideas for their next business venture.
We’ll use #3 as our example. We use Google Spreadsheets for this project, because Google Spreadsheets easily allows multiple people to edit the same spreadsheet at the same time.
Spreadsheet Inputs
We want to give each person their own tab to enter their three best ideas for submission to the group. Then we’ll eventually want them to input their votes on their favorite ideas.
First, we set up our Google spreadsheet – we click on the plus sign in the bottom left to make 5 separate worksheets, and rename each sheet with the name of a person in our 5 person group. Each person will enter in their top three ideas for business ideas:
Spreadsheet Logic and Outputs
Now we want to compile all the ideas on a master sheet, anonymously show each person every other team members’ ideas, and have each team member vote on the ideas that they think are best. Then we’ll sum up all the votes and come up with a final ranking of the group’s ideas.
First, compiling all the ideas into a master tab – This can be done by directly linking with the equals sign, but we used a vlookup to each sheet:
Now for assigning questions in random order to each person on their worksheet. Note that this can be done in many ways, here’s a very quick summary of how we did it:
1) First we created a column of question numbers applicable for each person – using an if statement to see if they wrote the question, if so, then we don’t copy the question number:
2) Then to randomly order everyone’s questions, we created a column where each entry is the rand() function. Then paste the random values into the next column with paste values (because otherwise the rand() function will recalculate with every future action). Then use rank() to come up with a randomized order of questions:
3) We add a question column to assign to each person that uses the random ranking number and returns blank if that question is not in their list on the left, or else looks up the question above. Then we link these to each person’s individual worksheet with a column for them to assign a total of five points to all their options:
4) Finally we bring in everyone’s points to the main page, sum them up, rank them, and sort them to get the group’s best ideas:
Check out our version of the sheet here – you can click File –> Make a copy to edit your own version!