12

Let's say I have a column of values (e.g., first names). There may be some duplicate data (some first names are very common).

How can I get the unique values?

In SQL I would do

SELECT DISTINCT first_name FROM... 

How can I do it in a Google Spreadsheet?

22

In Google Spreadsheets, you can use the UNIQUE() formula to do that.

Formula

=UNIQUE(A1:A10)

Explained

For the range as seen in the screenshot, there are 10 entries. The UNIQUE() formula accepts a range and filters out the duplicates and returns that range, see screenshot, leaving only 7 unique entries.

Screenshot

enter image description here

Reference

  • How does UNIQUE work exactly? In your example you specified the formula only in cell B1, yet values appear in B2, B3, B4, etc.. so clearly the UNIQUE function has some "magic" behaviour in that it alters cells around it even though they don't contain a value or formula. – Dai Feb 7 '17 at 2:48
  • @Dai In the past, the cells in which the formula had expanded to (like B2, B3 ... etc), contained formula's as well: CONTINUE(). With the new Sheets this is no longer the case. If it can't expand, then it will through an error: #REF! – Jacob Jan Tuinstra Feb 7 '17 at 17:17
  • 1
    This answer is a little misleading in that it shows an ideal case where all unique names appear before a duplicate is seen. – Richard Simões Sep 11 '17 at 3:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.