How do I get the distinct/unique values in a column in Excel?

Question

If I have a column with values, and I want to find out what distinct values are in there (not how many - but the actual distinct values), how can I do that?

In SQL Server I would do something like

SELECT Distinct(MyColumn) FROM MyTable

Answer 1

Simpler than you might think:

• Click the Data Ribbon Menu
• Select the Advanced Button in the Sort & Filter section
• Fill in the dialog Box, copying the results to another location and making sure you tick Unique records only

Answer 2

Excel 2007 and 2010:
Use the Remove Duplicates menu option under the Data header.

Excel 2003:
Easy way:

1. Make sure your data has a header
2. Data --> Filter --> Advanced Filter
3. Check Unique Records Only
4. Select Copy to another location
5. Click OK

Hard way:

Write a macro with the following code:

'Remove duplicates from sorted list
Sub getDistinct()
    Do While ActiveCell.Value <> ""
        If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
            ActiveCell.Select
            Selection.Delete Shift:=xlUp
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Loop
End Sub

That gives you your distinct list. You may want to copy your list to another sheet first.

Answer 3

Or (a simple crude way):

In B1,

=IF(COUNTIF(A$1:A1,A1)=1,A1,"") 

and copy down. It just copies the first occurrence of each value across (in the row that it occurs in).

Answer 4

I use two main methods to get distinct values - the first is the invaluable Remove Duplicates tool as suggested by Nathan DeWitt.

The downside to Remove Duplicates is that you lose data. Despite the power of Ctrl+z you may not wish to do this for various reasons - eg you may have filters currently selected that make it quite messy to remove duplicates to get your distinct list of values.

The other method, not mentioned above is to use a Pivot Table.

Let's say this is your table and you want to get the distinct list of Platforms.

Insert a Pivot Table with the table as your data source.

Now select the Platform(s) column as your Row Labels.

Voila! A list of distinct values.

If you wish to tidy this up, you can switch off Field Headers and set Grand Totals to be Off for Rows and Columns.

Answer 5

Or you can include the filter option in a macro

    Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True

Answer 6

For me, the most simple way to sort is to use "Filter" option.

First step is to find the duplicate values with Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.

Then click the arrow on the Column you have the duplicates (in this case "Platforms") -> Filter by Color (Cells color or font color).

Answer 7

In Excel 365 we now have UNIQUE(), which returns an array of unique rows.

Answer 8

Everyone has answered but i am going to share and explain what i have used

1- Assume your data starts in cell A1. Place your cursor in cell B1 (or any other column where you want the unique values to appear).

2- Enter the following formula in cell B1:

=UNIQUE(FILTER(A:A, A:A<>""))

This formula does two things: The FILTER function filters out empty cells from column A. The UNIQUE function then extracts unique values from the filtered result.

3- Press Enter. Excel will display the unique values from column A in column B, starting from B1, and will automatically expand to include all unique values.

Note: Create an empty column at right side of your existing column before applying the formula.