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).