Get a list of unique values in Excel & extract unique rows

This is the concluding segment of the Excel Unique Values series, detailing how to retrieve distinct or unique values from a column using formulas, along with customization tips for various data types. You'll also discover how to swiftly generate a distinct list using Excel’s Advanced Filter and extract unique rows via the Duplicate Remover tool.

In recent articles, we've explored multiple techniques to count and locate unique values in Excel. If you've followed those guides, you're already familiar with identifying, filtering, and copying methods. However, that approach can be time-consuming and isn't the only way to extract unique values. A much faster method involves using a specific formula, which I’ll demonstrate shortly—alongside several other efficient techniques.

Tip: For users of Excel 365 with dynamic array support, the easiest way to get unique values is by using the UNIQUE function.

How to extract unique values in Excel

To prevent confusion, let's first clarify what we mean by unique values. These are entries that appear only once in a dataset. For instance:

To pull out such values, use one of the following formulas:

Array formula (entered with Ctrl Shift Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1,$A$2:$A$10) (COUNTIF($A$2:$A$10, $A$2:$A$10)1), 0)), "")

Standard formula (entered with Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0,INDEX(COUNTIF($B$1:B1, $A$2:$A$10) (COUNTIF($A$2:$A$10, $A$2:$A$10)1),0,0), 0)), "")

Key references in these formulas:

• $A$2:$A$10 – the original data range.
• $B$1:B1 – the cell just above the first output cell. Since the result list starts in B2, we reference B1. If your output begins in C3, adjust this to $C$2:C2.

Note: Ensure the header in the cell above your list (e.g., B1) doesn’t repeat within the source data, as it could interfere with the formula.

Here’s the array version in action, extracting unique names from A2:A20:

Instructions to apply the formula:

• Adjust the ranges to match your dataset.
• Input the formula into the first cell of your output range (e.g., B2).
• Use Ctrl Shift Enter for the array version; press Enter for the standard one.
• Drag the fill handle down to cover the full range. Thanks to IFERROR, excess cells will show blank instead of errors.

How to get distinct values in Excel (unique 1^st duplicate occurrences)

Distinct values include all different entries—both truly unique ones and the first appearance of duplicates. Example:

Use these formulas to generate a distinct list:

Array formula (Ctrl Shift Enter):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")

Regular formula:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, INDEX(COUNTIF($B$1:B1, $A$2:$A$10), 0, 0), 0)), "")

Where:

• $A$2:$A$10 – source data
• $B$1:B1 – reference to the cell above the first result cell

Result example:

Extract distinct values excluding blanks

If your data contains empty cells, the basic formula may return zeros. Fix this by modifying the formula:

Array formula (ignores blanks):

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") IF($A$2:$A$10="",1,0), 0)), "")

Get only distinct text values (ignore numbers and blanks)

To extract text-only distinct values, use:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10&"") IF(ISTEXT($A$2:$A$10)=FALSE,1,0), 0)), "")

Remember: $A$2:$A$10 is your source, and $B$1:B1 refers to the cell above the first result.

Result comparison:

Extract case-sensitive distinct values

For case-sensitive data like passwords or usernames, use this array formula:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, FREQUENCY(IF(EXACT($A$2:$A$10,TRANSPOSE($B$1:B1)), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), ""), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10))), 0)), "")

Result with case sensitivity preserved:

How the unique / distinct formula works

This section is for users who want to understand the inner mechanics of these formulas.

While the formulas may seem complex, they all follow a common structure: combining INDEX and MATCH with COUNTIF or additional logic functions.

Let’s analyze the base distinct values formula:

=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$10), 0)), "")

First, IFERROR simply hides #N/A errors when the formula is dragged beyond the number of actual distinct values.

Now, the core components:

1. COUNTIF($B$1:B1, $A$2:$A$10) checks if each value in column A already exists in the growing list in column B. It returns an array of 0s (not found) and 1s (already listed).

   In B2:
   COUNTIF("Distinct", {"Ronnie"; "David"; ...}) → {0;0;0;...}
   Since no values are yet in the output, all are 0.

2. MATCH(0, ..., 0) finds the position of the first 0 in that array—i.e., the first value not yet added.

   So MATCH(0, {0;0;0;...}, 0) → returns 1.

3. INDEX($A$2:$A$10, 1) returns the first item: "Ronnie".

As the formula is copied down, $B$1:B1 expands to $B$1:B2, $B$1:B3, etc., so each new value is checked against all previously extracted distinct entries.

In B3:
COUNTIF({"Distinct";"Ronnie"}, ...) → {1;0;0;...}
Now "Ronnie" is marked as 1 (already listed), so the next 0 corresponds to "David", which gets pulled.

Tip: Highlight parts of the formula in the formula bar and press F9 to see intermediate results:

![](/uploads/20250724/17533203

The above is the detailed content of Get a list of unique values in Excel & extract unique rows. For more information, please follow other related articles on the PHP Chinese website!