How do I get the distinct 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

Excel 2007:
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 2

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 3

Or you can include the filter option in a macro

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

Answer 4

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