How to Get Unique and Distinct Values in Excel (2023)

In this article, we will explore various methods to extract unique and distinct values in Excel. We will cover different formulas and techniques that will help you achieve this task efficiently. Whether you need to get a list of unique values in a column or extract distinct rows from a table, we've got you covered.

Table of Contents

1. Introduction
2. How to Get Unique Values in a Column
   • Array Unique Values Formula
   • Regular Unique Values Formula
3. How to Get Distinct Values in Excel
   • Array Distinct Formula
   • Regular Distinct Formula
4. Extracting Distinct Values While Ignoring Blanks
5. Extracting Distinct Text Values While Ignoring Numbers and Blanks
6. Extracting Case-Sensitive Distinct Values
7. Using Excel's Advanced Filter to Extract Distinct Values
8. Extracting Unique and Distinct Rows with Duplicate Remover
9. Conclusion

1. Introduction

When working with data in Excel, it is often necessary to extract unique or distinct values from a column or table. Unique values are those that appear only once in a list, while distinct values include both unique values and the first occurrences of duplicate values. In this article, we will explore different methods to achieve this task.

2. How to Get Unique Values in a Column

To extract a list of unique values from a column in Excel, you can use either an array formula or a regular formula. Both formulas are based on the same logic of using the INDEX and MATCH functions in combination with the COUNTIF function.

Array Unique Values Formula

The array formula for extracting unique values is as follows:

=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)), "")

In this formula, the range $A$2:$A$10 represents the source list, and $B$1 is the top cell of the unique list minus 1. To use this formula, enter it in the first cell of the unique list (e.g., B2), and then copy it down as needed.

Regular Unique Values Formula

The regular formula for extracting unique values is as follows:

=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)), "")

This formula follows the same logic as the array formula but is completed by pressing the Enter key instead of Ctrl + Shift + Enter. Enter the formula in the first cell of the unique list and copy it down as needed.

3. How to Get Distinct Values in Excel

To extract distinct values in Excel, which include both unique values and the first occurrences of duplicate values, you can use similar formulas to the ones used for extracting unique values.

Array Distinct Formula

The array formula for extracting distinct values is as follows:

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

This formula uses the same range references as the unique values formula. Enter the formula in the first cell of the distinct list and copy it down as needed.

Regular Distinct Formula

The regular formula for extracting distinct values is as follows:

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

This formula follows the same logic as the array formula but is completed by pressing the Enter key instead of Ctrl + Shift + Enter. Enter the formula in the first cell of the distinct list and copy it down as needed.

4. Extracting Distinct Values While Ignoring Blanks

If your source list contains blank cells, the formulas we've discussed so far may return zeros for each empty row. To exclude blanks from the distinct values, you can modify the formulas as follows:

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

This modified array formula adds an additional condition to the COUNTIF function to exclude blank cells from the distinct list.

5. Extracting Distinct Text Values While Ignoring Numbers and Blanks

To extract distinct text values while ignoring numbers and blanks, you can modify the formulas further:

=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)), "")

This modified array formula adds the ISTEXT function to check whether a value is text. It excludes non-text values, including numbers and blanks, from the distinct list.

6. Extracting Case-Sensitive Distinct Values

If you're working with case-sensitive data, such as passwords or file names, you may need to extract case-sensitive distinct values. For this, you can use the following 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)), "")

This formula uses the EXACT function to compare values case-sensitively and extract distinct values accordingly.

7. Using Excel's Advanced Filter to Extract Distinct Values

If you prefer a more user-friendly approach, you can use Excel's Advanced Filter feature to extract distinct values. Here's how:

1. Select the column of data from which you want to extract distinct values.
2. Switch to the Data tab and click the Advanced button in the Sort & Filter group.
3. In the Advanced Filter dialog box, select the "Copy to another location" option.
4. Specify the source range and the top cell of the destination range.
5. Check the "Unique records only" option.
6. Click OK to extract the distinct values to the specified location.

8. Extracting Unique and Distinct Rows with Duplicate Remover

If you want a more comprehensive solution for extracting unique and distinct rows from a table, you can use the Duplicate Remover add-in. This tool combines the power of Excel formulas with the simplicity of the Advanced Filter feature. It allows you to find and extract unique rows based on values in one or more columns.

To use the Duplicate Remover add-in:

1. Select any cell within your source table.
2. Click the Duplicate Remover button on the Ablebits Data tab.
3. Follow the steps in the Duplicate Remover wizard to select the value type, columns to check, and the action to perform on the found unique values.
4. Specify the location where you want to copy the unique rows.
5. Click Finish to extract the unique and distinct rows.

9. Conclusion

In this article, we have explored various methods to extract unique and distinct values in Excel. Whether you need to get a list of unique values in a column or extract distinct rows from a table, you now have a range of formulas and techniques at your disposal. By using these methods, you can efficiently manage and analyze your data in Excel.