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
Excel 2007 and 2010:
Remove Duplicates menu option under the Data header.
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.
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.
Pivot Table with the table as your data source.
Now select the Platform(s) column as your
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.
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).
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?