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.

Sample Data

CLICK HERE for some sample data if you would like to try this out.

Duplicates

Sometimes Google Forms submits a Form twice causing duplicate lines in the spreadsheet. Sometimes people fill out the Google Form more than once.

Unique

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).

Paste Special

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.”

Rand

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.

Sort Range

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!