Microsoft KB Archive/110141

XL: Finding Unique Elements in a Range

---

The information in this article applies to:

• Microsoft Excel for the Macintosh, version 4.0
• Microsoft Excel for Windows, versions 4.0, 4.0a

---

SUMMARY

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

• Define the range of cells containing the values as a database. (A Data Extract of unique records can only be performed using a blank criteria range.)
  

NOTE: This method requires that the values in question be in one column only.

-or-

• Use an array formula to determine if a duplicate value already exists in the range of cells to be evaluated.

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.00a howto list

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

Last Reviewed: August 26, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use.