How to find the unique items in a list

by | Apr 11, 2018 | Excel Tips & Tricks

How to find the unique items in a list

Dissecting and organizing data doesn't have to be challenging. You can quickly filter your data items with this method using a combination of formulas to determine which are unique items.

Syntax

=LOOKUP(2, 1/(COUNTIF(expanding unique list, original list)=0), original list)

Steps

  1. Begin by typing in =LOOKUP(
  2. Follow with 2, and 1/COUNTIF(
  3. Select or type in the range reference that is one cell above the list, and make sure that the argument entered is an absolute reference (i.e. $D$2)
  4. Type in a colon and the same reference as the relative reference (i.e. :D2)
  5. Select or type in the range reference that contains the original list (i.e. $B$3:$B$9)
  6. Type in )=0),
  7. Select or type in the range reference that contains the original list (i.e. $B$3:$B$9)
  8. Type ) and press Enter to complete the formula
  9. Copy down the formula all the way down =LOOKUP(2,1/(COUNTIF($D$2:D5,$B$3:$B$9)=0),$B$3:$B$9)

How

With this detailed formula we aim to find a unique value, which is not listed in any previous cells (i.e. $D$2:D5), by searching for a greater value (2) in an array of 1 and 0 values (1/(COUNTIF($D$2:D5,$B$3:$B$9)=0)).

Let's breakdown the formula,

=LOOKUP(2,1/(COUNTIF($D$2:D4,$B$3:$B$9)=0),$B$3:$B$9)

Step Description Return Value
1 COUNTIF returns an array that contains 1 for each existed value of $B$3:$B$9 in expanding $D$2:D4 and 0 for not existed {0;1;0;0;1;1;1}
2 By using an equal sign, the 1/0 array is converted into a TRUE/FALSE array {TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}
3 The number 1 is divided by the array, creates an array of 1s and  #DIV/0 errors {1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!}
4

The number 2 is used as a larger number and is searched in the 1/error array.

The LOOKUP returns the value in original list by using last non-error matched values index.

 4th item in original list is pea