XL: Finding Unique Elements in a Range

ID: Q110141

The information in this article applies to:

SUMMARY

In Microsoft Excel, there are two methods you can use to find all the unique elements in a range of cells:

MORE INFORMATION

Example of Defining the Records in a Database

1. Enter the following in a new worksheet:

   A1  FIELDNAME     B1  FIELDNAME        C1  FIELDNAME
   A2  Lori          B2                   C2  Lori
   A3  Frank         B3                   C3  Frank
   A4  John          B4                   C4  John
   A5  Frank
   A6  Lori
   A7  Lori


   E1:    DATABASE      F1:     CRITERIA      G1:       EXTRACT
   E2:    RANGE         F2:     RANGE         G2:       RANGE

2. Arrange the data such that it is all in one column.

3. Insert a field name at the top of the column of data.

4. Select the field name and data. From the Data menu, choose Set Database.

5. Copy the field name only from the top of the database column and paste

   the name into the cell at the top of an adjacent blank column.

6. Select the cell where the field name has been pasted and also select
   the blank cell directly below. From the Data menu, choose Set
   Criteria.

7. Paste the copied field name again in another blank cell on the
   worksheet (allow enough room below the pasted field name for unique
   values extracted from the database range).

8. With this cell selected, choose Set Extract from the Data menu.

9. From the Data menu, choose Data Extract and select the Unique Records

   only check box in the Extract dialog box. All unique values in the
   column of original data should appear in the Extract range.

Example of Using an Array Formula to

Determine If a Duplicate Value Already Exists

1. Enter the following data into a new worksheet:

      A1  NAMES       B1  UNIQUE_SET
      A2  Lori        B2  Lori
      A3  John        B3  John
      A4  Frank       B4  Frank
      A5  John
      A6  Lori

2. In cell B2, enter the following formula as an array :

      =IF(SUM((A2=$B$1:B1)*1)=0,A2,"")

Fill the array formula down to cell B6. The unique set of values, will be displayed in cells B2:B6.

Additional query words: 4.00 4.00a howto list

Version           : WINDOWS: 4.0; MACINTOSH: 4.0, 4.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: February 4, 1998