¿Cómo encontrar valores únicos usando VBA en Excel?

How to find unique values using VBA in Excel?

Finding unique values using VBA in Microsoft Excel In this article, we will create custom function to find unique values from the defined range. Raw data for this example consists of duplicate entries of country name in the range A8:A21. Logic explanation We have created a custom function “UniqueItem” to extract the unique values from the range.

UNIQUE is a new function in Excel 365 and like other dynamic array functions has a few specificities that you should be aware of: If the array returned by UNIQUE is the final result (i.e. not passed to another function), Excel dynamically creates an appropriately sized range and populates it with the results.

In situation when you want to compare two or more columns and return the unique values between them, include all the target columns in the array argument. For instance, to return the unique First name (column A) and Last name (column B) of the winners, we enter this formula in E2: =UNIQUE (A2:B10)

Each item in the collection holds a specific index, starting from 1. Items (also can be called elements) of a collection do not have to share the same data type. In this function, we create a collection object and assign two values to it. Then the collection object is returned by the function.

We have created a custom function “UniqueItem” to extract the unique values from the range. This function takes two parameters as input. First parameter is the range which consists of the duplicate data and second parameter defines the index number of a unique value in the unique collection.

Second parameter is an optional parameter, if second parameter is not specified then function returns the number of unique value in the defined range. If the value defined for the second parameter is greater than number of unique values in the range then last unique value is return by the function.

This array is returned directly to the UNIQUE function as the array argument. UNIQUE then removes duplicates, and returns the final array: UNIQUE and FILTER are dynamic functions. If data in B5:B16 changes, the output will update immediately.