Office Tips 001: Excel, Find Unique Value

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

  1. Select the data to be filtered.

    Capture3.PNG

    Fig 1. A column of names.

  2. Go to DATA tab, in the Sort & Filter section, click on Advanced.

    Capture4.PNG

    Fig 2. Select filter tool.

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

    Capture5.PNG

    Fig 3. Adjust Advanced Filter options.

  4. Ta-Da! A list of unique values will appear at the cell that you have selected.

    Capture6.PNG

    Fig 4. Unique values output.


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.

  1. Create a column of a single value “1” beside the column of data.

    Capture7.PNG

    Fig 5. Add a count beside each value.

  2. Go to INSERT tab, in the Tables section, click on PivotTable.

    Capture8.PNG

    Fig 6. Select table tool.

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

    Capture9.PNG

    Fig 7. Adjust PivotTable options.

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

    Capture10.PNG

    Fig 8. Display PivotTable fields.

  5. There! You now have a pivot table with the number of occurrences for each value in your data.

    Capture11.PNG

    Fig 9. PivotTable output.


These are definitely not the most elegant solutions, but it will solve your immediate problems.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s