Using the UNIQUE function
The UNIQUE function is a new function for the latest version of Excel. It is a dynamic function that updates data automatically. When we use the Remove Duplicates feature to get rid of duplicate data, we can’t update the table to refresh it with new data.
To remove duplicate data using the UNIQUE function, follow these steps:
- Open the workbook named Unique.xlsx.
- On the sheet named Unique1, you will see an employee list. Duplicates have been purposefully added to this dataset. If you would like to see the duplicate values, sort the data. We will use the UNIQUE function to return unique values from the list.
-
Let’s investigate the syntax of the UNIQUE function:
- The array is the range from which we need to return unique values.
- [by_col] is how you would like to compare the data, either across rows or columns (optional field).
-
[exactly_one] is the logical value that defines which values are unique using the TRUE or FALSE condition. TRUE returns values that occur once, while FALSE returns all distinct values in the range (optional field).
For this example, we will remove the duplicate values, so only the array is required.
- First, click where you would like the unique result to be placed – for this example, we will use cell J2.
- Press = to start the formula, then type UNIQUE(.
- Select the A2:G95 range, then press Enter to display the result.
-
Any amendments you make to the source dataset will update in the unique
dataset. If you add rows to the dataset, you may want to format the dataset as a table so that the new rows are included and update the unique dataset accordingly.
Let’s look at another example.
If we wanted to return a list of employees who have not attended more than one training session from a list of training data, we would need to alter the formula:
-
Open the Unique2 sheet. The dataset contains courses that employees have attended. We would like to extract the employees who have only attended one out of the three training sessions provided.
- We will place the result in cell H2.
- Press = to start the formula.
- Type UNIQUE( as the function.
- Select the A2:F67 range.
- Add a comma, then another comma, to skip the [by_col] argument.
- Type or select TRUE, then add the closing parenthesis.
- Press Enter to display the result. As we can see, only three employees have not completed the required number of courses:
Figure 9.55 – The result of the UNIQUE formula
Another issue that impacts formulas and many other features in Excel is blank cells within a dataset. In the next section, we will look at ways to remove rows and fill in blank cells quickly. Remember that we can use the filter command to filter blank rows too.
