Unique Entries in a Range in Excel

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.