Spreadsheet Practice

Google Sheets lesson 5 of 11, Sheets power tools

UNIQUE, COUNTUNIQUE and duplicates

List each value once, count how many different values there are, and flag the repeats before they cause trouble.

ProSheets mode9 minutes, 3 exercisesUNIQUESORTCOUNTUNIQUECOUNTIF

Watch it done

5 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

UNIQUE returns each different value in a range once, in the order it first appears: =UNIQUE(C2:C9). The results fill the cells below, so leave them empty. Wrap it in SORT for an alphabetical list: =SORT(UNIQUE(C2:C9)). With only a range, SORT orders by the first column, A to Z.

COUNTUNIQUE counts the different values without listing them: =COUNTUNIQUE(B2:B9) tells you how many people signed up, however many times each one did. It is a Google Sheets function; Excel has no direct equivalent.

To find duplicates, count each value against the whole column: =COUNTIF($B$2:$B$9, B2)>1 is TRUE for every email that appears more than once. Lock the range with $ so it stays put as you fill down, and leave B2 unlocked so it moves to B3, B4 and so on.

To keep the first copy and flag only the extras, let the range grow as you go: =COUNTIF($B$2:B2, B2)>1. In row 5 it only looks at B2:B5, so the first time an email appears the count is 1. The same formula works as a conditional formatting rule: select the column, choose Format, then Conditional formatting, pick "Custom formula is", and enter it to highlight the repeats.

Syntax

=UNIQUE(C2:C9)Each value once, in first-seen order
=SORT(UNIQUE(C2:C9))Each value once, A to Z
=COUNTUNIQUE(B2:B9)How many different values
=COUNTIF($B$2:$B$9,B2)>1TRUE when this value appears more than once
=COUNTIF($B$2:B2,B2)>1TRUE only for the second and later copies

Watch out for

  • UNIQUE needs empty cells below it for its results. Anything in the way stops it with an error.
  • Without the $ signs, the COUNTIF range slides down as you fill and starts missing earlier rows.
  • "jo@fernhill.com" and "jo@fernhill.com " with a trailing space are different values. Clean the column with TRIM first if counts look high.
  • Data, then Data cleanup, then Remove duplicates deletes rows for good. A formula flag lets you check before anything is removed.

The exercises

  1. Workshops on offer. In F2, list each workshop from C2:C9 once, sorted A to Z. Use one formula.
  2. How many, not counting repeats. In I2, count how many different people signed up, using the emails in B2:B9. In I3, count how many different workshops there are in C2:C9.
  3. Flag the repeats. In E2, show TRUE when the email in B2 appears more than once in B2:B9, and fill down to E9. In F2, show TRUE only for the second and later copies of an email, so the first copy stays FALSE. Fill down to F9.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Workshops on offer
  2. How many, not counting repeats
  3. Flag the repeats
Try these functions in the Formula Lab