Google Sheets Functions – UNIQUE, COUNTUNIQUE, SORT

In this post we’ll look at how we can remove duplicates from a set of data, with one simple formula using the UNIQUE function. We’ll also use the SORT function to put the unique list in order and use this in a drop down menu using DATA VALIDATION. Finally, we’ll look at counting those entries by using COUNTUNIQUE.

So in detail, we’ll look at the following:

• Using the UNIQUE function to list unique occurrences in a list
• Using UNIQUE with SORT to sort the unique list
• Making a drop down menu from a list
• Making an alphabetical drop down menu from a list
• Using COUNTUNIQUE to count how many things you have in the list, whilst ignoring duplicates
• Using UNIQUE to look for unique occurrences with 2 or more criteria

---

Let’s use a list of books as an example. Here I’ve got a list of the copies of books we have. In reality this is close to 2,000 books, but we have multiple copies of most of the books.

---

Example 1 – Using the UNIQUE function to list unique occurrences in a list

I want a list of what books we have without having the duplicates included in it. In a spare cell, I write the following formula:

=UNIQUE(A2:A16)

This looks at the list of books and returns 1 instance of each one. In this example, we have 9 different books.

---

Example 2 – Using UNIQUE with SORT to sort the unique list

I can also sort the above list into alphabetical order by wrapping the unique function up in a SORT function. I write the following formula:

=SORT(UNIQUE(A2:A16),1,TRUE)

The SORT function contains three parts:

range, column to sort (number), is it to be sorted in ascending order

So we put the UNIQUE function in the range part, then tell it’s column 1 (in fact there is only one column), and we put TRUE in the final part to sort it in ascending order, i.e. A to Z.

Here we have the list sorted.

---

Example 3 – Making a drop down menu from a list

One reason why I often do this, is that I use this list in a drop down menu. So, when I add books to the list, I don’t have to type in the full names every time, I just select the book I want from the list.

To create a drop down list, you need to add ‘Data validation’ to the cell. It’s sounds awfully technical, but really it just means it limits what you can write in the cell, and in this case, it will expect a book name from the list.

Right-click on the a spare cell, then select “Data validation” from the bottom of the list.

This takes you to the Data validation dialogue box. The main part to use is the box to the right of where it says “List from a range”. Click the little grid in the box.

This asks you to add the range you want. You can either type it in or just select the cells you want and the range gets added automatically. Click OK.

This has added the range of books, now click OK.

In the cell, you will now see a little inverted triangle. This is the drop down menu symbol.

Clicking on the triangle will open the drop-down menu and your list of books. Note, that as I’ve used the original list of books, this has automatically made a unique list of the books, i.e. there are no duplicates. The only thing is, is that it isn’t sorted alphabetically, just in the order the books first appeared in the original list.

---

Example 4 – Making an alphabetical drop down menu from a list

This is where our sorted list comes in. I right-click on a spare cell and select Data validation as before. This time I enter the range where the sorted unique book list is, in this case in cells E2 to E17. Click OK.

This time, clicking on the triangle, opens the same list but this time sorted alphabetically, which is much more useful and easier to use.

---

Example 5 – Using COUNTUNIQUE to count how many things you have in the list, whilst ignoring duplicates

This time I want to know how many different books I have in the original list. I write the following formula:

=COUNTUNIQUE(A2:A16)

As you can see, it’s correctly identified that there are in fact 9 different books.

---

Example 6 – Using UNIQUE to look for unique occurrences with 2 or more criteria

All the examples above looked at a single column. UNIQUE can in fact look across multiple columns. In our book example, I’ve just looked at the name of the book, but in fact these books come in different levels and quite often I need to know how many of a specific book and level we have, to make sure there are enough copies for all the teachers.

Here’s the list I’m going to use. It has the book title and the level of the book.

In cell D2 I write the following formula:

=SORT(UNIQUE(A2:B15),1, TRUE)

This is the same formula as example 2, except that the range now includes column B. This finds unique occurrences where both column A and B together are unique. Therefore, it includes 3 examples of the book “Business Result”, but each one is a different level.

If we want an alphabetical list, then we modify the formula, to add a second sort column:

=SORT(UNIQUE(A2:B15),1, TRUE,2,TRUE)

This sort column 1 alphabetically A to Z, then column 2 A to Z.

One final thing I often do is, to include a range which is longer than the current range or an open-ended range. This means that if I add some new books in the original list, they will automatically be seen by the UNIQUE function, rather than have to edit the range every time a book is added. This is because UNIQUE ignores spaces.

So the formula would be something like this:

=SORT(UNIQUE(A2:B),1, TRUE)

Note, that column B has no number as it’s looking at the whole of column B, so if anything is added into that column or in fact column A, it will automatically be included in the search range of the unique list.

---