It is very common to require a list of unique values from a list that contains duplicates. While it may be easiest to take a single approach to get these values, there are cases where a different approach may be desirable.
Three different approaches will be considered in this post:
- Creating a pivot table to create a list of unique values.
- Using the UNIQUE() formula.
- Using Remove Duplicates in the data tab.
The Data Set
The data that will be used in this example is from a listing of winning and losing pitchers for the Toronto Blue Jays in 2019.
The goal of this posting is to come up with different methods for creating a list of unique pitchers who had a win or loss in 2019.
Getting unique values by creating a Pivot Table
A pivot table is created by selecting PivotTable under the Insert menu.
Since the objective is to create a list of unique values, there is no need to create a new worksheet. By selecting a single cell, the pivot table will default to a location on the existing worksheet.
To create the PivotTable, the data source from the worksheet should be used. Picking all the cells in the data table will provide this data.
By selecting the DecidingPitcher as the row, it will create a list of all unique deciding pitchers within the list.
Use the UNIQUE() formula.
The UNIQUE() formula is new as of February 2020 in Office 365, and provides a list of the unique values.
Microsoft describes the formula as follows:
The UNIQUE function returns a list of unique values in a list or range.
https://support.office.com/
This formula is simple to use; all you need to do is enter UNIQUE, followed by the range (or table column) that you want to get unique values from.
This formula creates a list of unique values from the list provided, in the order that they appeared in the original list.
Copy data and remove duplicates.
Under the data menu, there is an option to remove duplicates. However; since the data is stored in a table, this feature cannot be used.
First, you need to copy the data from the data table into a new column.
Since the data is no longer in a data table, the remove duplicates function can be used to get unique values.
Next, select any cell in the new column (column E in the example above). There should be no need to change any default settings in Remove Duplicates.
This will remove all duplicate values from the list, leaving a list with only unique values.
When to use each method
PivotTable
The pivot table works best when there is need to aggregate values. If there was a need to perform further calculations; such as adding up total wins, it is best to work with a Pivot Table.
This data source also refreshes as the underlying data changes, so it can be effective if the data changes frequently; like using the list for the current season.
A major disadvantage is that the list is sorted alphabetically, which creates a challenge if data is required in the same order as the source data.
Users may also be concerned that a PivotTable is a complex solution for the simple problem. Many users find PivotTables very difficult to understand, and using one for a simple problem could meet resistance.
UNIQUE formula
The UNIQUE formula has a number of advantages; it is easy to use and can return the data in the same order as the original table.
Like PivotTables, the data in the UNIQUE formula updates as the underlying data changes.
The major disadvantage of the UNIQUE formula is that it is only available in Office 365, and the function is very new. This disadvantage is likely to disappear over time.
If calculations are required (such as a COUNTIFS() calculation), use a Pivot Table.
Remove Duplicates
The remove duplicates function is a simple method that is available in all versions of Microsoft Office. This process quickly provides values for other follow up calculations where a PivotTable is too complex.
However; the major downfall of the remove duplicates function is that data does not refresh.
Remove Duplicates works best when the unique values are for a one-time analysis. This process also is optimal on versions of Office prior to O365 / 2019 where the UNIQUE function is unavailable.