The UNIQUE function in Excel
In today’s post I want to share with you a helpful Excel function called UNIQUE. The UNIQUE function allows you to extract a unique list of items. It can work with various types of data including text, numbers, dates, and times.
This example uses SORT and UNIQUE together to return a unique list in ascending order. The graphic below is a simple table for commissions paid used as the source data. If you format the range as an Excel Table, then the formula will automatically update when you add or remove data from the table.
- In this example, we want to know which states our personnel are working in
- Add the formula to the side of the table using both SORT and UNIQUE together to get a list in ascending order. For the above example, the formula would be =SORT(UNIQUE(E3:E15)).
- Now we get a list of states in ascending order
If you use a table, your formula will be converted to look like this:
- Now we can add to our table and our list of States will automatically update
You can also explore how to combine the UNIQUE function with other functions to achieve specific results.
Here’s an example: We want to filter the personnel column based on the commission condition, and then extract unique personnel names. The result provides a list of unique personnel names with commissions less than 100 and those greater than or equal to 100.
Explanation of the formula below
- FILTER(Table1[PERSONNEL], (Table1[COMMISSION]<100)):
The FILTER function extracts rows from the PERSONNEL column in the table named Table1 based on a condition. The condition is that the COMMISSION value in the same row must be less than 100. This filtered list contains personnel names meeting the specified criteria.
The UNIQUE function takes the filtered list (from the FILTER function) and extracts unique values. It removes any duplicate personnel names, leaving only distinct names.
The entire formula combines these steps: filter the PERSONNEL column based on the commission condition, and then extract unique personnel names. The results are lists of unique personnel names with commissions less than 100 and greater than or equal to 100.
Consider using these techniques to analyze and summarize your data in your Excel spreadsheets. As you know there are many ways to get the correct data in excel. I hope this helps and provides you with a new formula to use. Feel free to customize these formulas based on your specific context.
As always, if you need assistance with any Vista processes, please visit our website
ConstrucTech Consulting and Book a Call with one of our consultants.
