Find Unique Entries in Google Sheets
When the 2nd Google Classroom book that Libbi Miller and I wrote came out, we put out a Google Form to win a copy of the book. Before choosing a winner I needed to exclude duplicate entries. Here is how to do this.
CLICK HERE for some sample data if you would like to try this out.
Sometimes Google Forms submits a Form twice causing duplicate lines in the spreadsheet. Sometimes people fill out the Google Form more than once.
In a blank cell, either on the same sheet or in a new sheet, use the formula =unique(). Inside of the parenthesis put the range of data that has duplicated values. For example, in my sample data this is column C. I clicked on column C to enter the range of C:C. =unique(C:C).
In order to use the list of unique data and sort it, I need to strip out the formula I just used. Select the data in the unique list and copy it (Control C).
Right on top of the copied list, right-click and choose “Paste special values only.”
You now have a unique list you can sort. In the column next to the list of unique values, type the formula =rand(). This assigns a random decimal. If you click back on that cell where you input the variable, you can double click on the fill down square in the bottom right. This copies the =rand() to everyone in the list. If that does not work, click and hold down on the fill down square and pull DOWN.
You want to sort just the range of unique values and random numbers. Highlight these two columns together. Right-click and choose “Sort range.” Your random number is the 2nd column so you need to change the “sort by” from the first column to the second column. The person who sorts to the top is your winner!