Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
share|improve this question

5 Answers 5

up vote 71 down vote accepted

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

enter image description here

share|improve this answer
10  
For those of us still living a ribbon-free existence, it's Data->Filter->Advanced. –  J.T. Grimes Oct 1 '09 at 16:39
7  
+1 This method is superior to Remove Duplicates if you want to keep the original data intact. –  Excellll Jul 13 '12 at 0:58

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.

share|improve this answer
14  
Thanks! This is a proof that Stack Exchange is way better than any other source for technical information. All other google results are useless, and unranked. Also I wonder how experts-exchange survive –  Eran Medan Mar 23 '11 at 2:53

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. dummy table

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.

pivot table as distinct value list

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

share|improve this answer
    
The PivotTable technique is brilliant! Thanks for this. –  Thomas L Holaday Apr 17 at 13:53

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

share|improve this answer

Or you can include the filter option in a macro

    Columns("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
share|improve this answer

protected by studiohack Aug 16 '11 at 15:40

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.