Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Find Unique Entries

Google Sheets pick a winner
Google Sheets: Find Unique Entries

Google Sheets pick a winner

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.
Google Sheets duplicate entries

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).
duplicated data use unique formula

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.”
copy and paste special values

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.
=rand

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!
right click and choose sort range

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.