Advanced filter allows you to extract (display) only those data that meet some criteria. Filter and Advanced filter are basic functions that allow filtering. Here we will deal with advanced filtering, and only by a specific situation, when we want to single out unique and unique values.
Of course, in a Excel, it’s possible to end the job in many ways and this is probably the simplest.
Advanced Filter – Uniqueness Separation
We’ll use a list of repetitive names. In the first picture we can see the difference between unique different values and unique values. Unique different values can appear several times in the table, and we often need them. Excel has limitations in standard filtering options that can only display 5000 unique values.
To extract all unique different values, we will use an advanced filter – the Advanced filter located on the Data toolbar.
- First, select the column that contains the information you want to process.
- Start an advanced filter (Data / Advanced in the Sort & Filter toolpart)
- Select the copy option (Copy to another location)
- Select the location where you want to copy the values (Copy to :)
- Select the Unique records only option and the last one is OK.
When finished, you will get the result as in the previous figure in the Various values column.
Now it is left to use the formula COUNTIF to count the unique different values in the initial table and filter them with the filter option.
In the concrete case, the formula in the column shows the number of impressions:
=COUNTIF(F5:F9,G5)
The first data in the function are all those fields that we will count, and the second parameter is the condition that will enter the final sum. When the condition is equal to the content in the cell then it is sufficient to state only which cell is involved. In this case, the content is in the G5 cell.