Microsoft KB Archive/110141

{|
 * width="100%"|

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.

Example of Defining the Records in a Database
  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  Arrange the data such that it is all in one column. Insert a field name at the top of the column of data. Select the field name and data. From the Data menu, choose Set Database. 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. 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. 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). With this cell selected, choose Set Extract from the Data menu.</li> 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.</li></ol>

Example of Using an Array Formula to

Determine If a Duplicate Value Already Exists
<ol>  Enter the following data into a new worksheet: <pre class="FIXEDTEXT">     A1  NAMES       B1  UNIQUE_SET A2 Lori        B2  Lori A3 John        B3  John A4 Frank       B4  Frank A5 John A6 Lori </li>  In cell B2, enter the following formula as an array : "=IF(SUM((A2=$B$1:B1)*1)=0,A2,'')"</li></ol>

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 :