Sometimes the UNIQUE function won’t return what you’re expecting when you refer to a range of cells. You can use another function to allow you to extract the unique individual values from a range.
Shout out to Wyn Hopkins for pointing me to TOCOL as the solution for another issue.
In the image below we have a two-column list of codes.
If we want to extract the unique codes from the list we could use the UNIQUE function, but the result is not what we are expecting – see image below.
The UNIQUE function is looking for unique entry combinations across the rows. That technique is useful if you are looking at two columns that are related to each other like first name and last name.
In our case we need to identify the unique entries from each of the cells. We can use the TOCOL function to convert the range into a column of entries.
You can see how the TOCOL function works in the image below.
We can now put the TOCOL function inside the UNIQUE function to get the unique list that we need.
The TOCOL function has a few options that may be useful in other settings – see image below.
You can see the second argument has options to avoid blank cells and errors within the range.
Someone on LinkedIn told me this just yesterday. Like you, I thought UNIQUE only applied to single rows or single columns of data; however, when you give UNIQUE a multi-column range, it will remove the duplicate rows! For the data you show, change some of the rows to be duplicates of other rows and then apply UNIQUE to the multi-column range and watch the extra, duplicated rows disappear.
Hi Rick
Yes that is the standard UNIQUE treatment of a range – unique combinations. I include that use of UNIQUE in my training using first and last names to get unique names.
This post was about treating the cell entries as unique rather then their row combinations.
Ah, it was not new to you… but it was new to me (the problem with being entirely self-taught).