How do I get the distinct/unique values in a column in Excel?
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
Excel 2007 and 2010:
Use the Remove Duplicates
menu option under the Data header.
Excel 2003:
Easy way:
- Make sure your data has a header
- Data --> Filter --> Advanced Filter
- Check Unique Records Only
- Select Copy to another location
- 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.
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).