The following shows the fastest way (probably!) to filter out the unique values in a list that contains duplicates, and to count the number of occurrences of each unique value.
(Excel Version: Office 2013).
Find Unique Values
- Select the data to be filtered.
- Go to DATA tab, in the Sort & Filter section, click on Advanced.
- In the Advanced Filter options,
– Select Copy to another location
– Select the cell to copy the unique values to, using the Copy to: option
– Check Unique records only
– Click OK
- Ta-Da! A list of unique values will appear at the cell that you have selected.
Number of occurrences for each value
For this to work, you will have to modify the data sheet a little bit and make use of a pivot table.
- Create a column of a single value “1” beside the column of data.
- Go to INSERT tab, in the Tables section, click on PivotTable.
- In the PivotTable options,
– Under Choose the data that you want to analyze,
– Select Select a table or range
– Select both column of data, using Table/Range: option
– Under Choose where you want the PivotTable report to be placed,
– Select Existing Worksheet
– Select a cell to output the results, using Location: option
- Go to PivotTable Fields that appear on the right of the window,
– Under Choose fields to add to report: option,
– Check both check-boxes that belong to the header of your data columns
- There! You now have a pivot table with the number of occurrences for each value in your data.
These are definitely not the most elegant solutions, but it will solve your immediate problems.