Good day, this refers to Question 3 in the downloadable exercise file for lesson 3.62 on the Unique formula. The following is observed - exactly the same results are returned when the parameter for [exactly once] part of the formula is "true" or "false". After investigating further, it seems that the snag comes in when the formula is applied across more than one column.
Example 1 - Both the formula "=UNIQUE(Table_2q[[IT Area]:[Region]],FALSE,FALSE)" and "=UNIQUE(Table_2q[[IT Area]:[Region]],FALSE,TRUE)" return the same results below.
| IT Area | Region |
| Functional | Asia Pacific |
| Functional | Europe |
| Functional | USA |
| Manufacturing | Asia Pacific |
| Manufacturing | Europe |
| Manufacturing | USA |
| Development | Asia Pacific |
| Development | Europe |
| Development | USA |
| Enablement | Asia Pacific |
| Enablement | Europe |
| Enablement | USA |
| Support | USA |
Example 2 - Things are different however when the formula is applied to a single column in a table, but differs when applied to a single column, when the data is not formatted in a table.
| Data is in a table | |
| Exactly once = False | Exactly once = True |
| IT Area | IT Area |
| Functional | #CALC! |
| Manufacturing | |
| Development | |
| Enablement | |
| Support | |
and...
| Data is not in a table | |
| Exactly once = False | Exactly once = True |
| IT Area | IT Area |
| Functional | Support |
| Manufacturing | |
| Development | |
| Enablement | |
| Support | |
This means that the Unique function is not reliable across columns and returns erroneous results when applied to a table.
Please note that the cell with the #CALC! error contains a formula that refers to the Region column.
=UNIQUE(Table_2q[Region],FALSE,TRUE)
Since there are no Regions that are both unique and distinct #CALC! is returned.
Change the formula to =UNIQUE(Table_2q[IT Area],FALSE,TRUE) and it will work the same as the formula referencing a regular cell range.
Yes, I made an error in the column selection but I still think that the Unique function does not work as expected when applied across multiple columns in a table.
What makes you say that? Is it because in the Q3 example both FALSE, FALSE and FALSE, TRUE give the same result?
If so, that's just a coincidence. Unique rows in the data set also happen to be distinct.
To test this, add another row to the example data for " Finance, Support, USA, $ ". Then "Support, USA" will be included once in the FALSE, FALSE formula as it is a unique combination for IT area and Country. However, it will not show up in the FALSE, TRUE formula as "Support, USA" is not distinct. Thus, the UNIQUE function works as expected, even when applied to multiple columns.
