Friday, December 9, 2016

Excel VBA unique function

There is no built-in Excel function that can return unique values of range or array of values. In this post I will show the easiest method that can return unique values of array or range.

Consider the following example: In Range("A1:A10") we have 10 strings and we want to write the unique ones in column "B". Hereunder the code:





Private Sub GetUniqueValues()

UniqueValues = "-"      ' Delimited string. Delimiter "-" which used as a bullet. You may use different delimiter

' ########### Get unique values from range("A1:A10") ##############
For i = 1 To 10
If InStr(UniqueValues, "-" + CStr(Cells(i, 1))+"-") < 1 Then     ' If cell value is not found in the unique values delimited string then add it
UniqueValues = UniqueValues + CStr(Cells(i, 1))+"-"
End If
Next i

' Create the array of unique values
UniqueArray = Split(mid(UniqueValues,1,len(UniqueValues)-1), "-")

' Write unique values to column "B"
Application.ScreenUpdating = False
For i = 1 To UBound(UniqueArray)
Cells(i, 2) = UniqueArray(i)
Next i
Application.ScreenUpdating = True

Debug.Print UBound(UniqueArray)     'Print down number of unique values in the immediate window
End Sub
 
This method has the following Pros and Cons...


Pros:

[1] This method is one of the easiest methods to get unique values
[2] It works for both strings, numbers, or mixed data of strings and numbers
[3] The best solution to populate lists of combo boxes and list boxes
[4] You may make a little modification to code to ignore case-sensitive and extra leading/trailing/in-between blanks easily. The code -after modification- will look like the following
[5] Easily, you can count number of unique values
[6] It can be used to highlight unique values



Private Sub GetUniqueValues()

UniqueValues = "-"      ' Delimited string. Delimiter "-" which used as a bullet. You may use different delimiter

' ########### Get unique values from range("A1:A10") ##############
For i = 1 To 10
If InStr(UCase(UniqueValues), "-" + UCase(Trim(Cstr(Cells(i, 1))))+"-") < 1 Then     ' If cell value is not found in the unique values delimited string then add it
UniqueValues = UniqueValues+ Trim(Cstr(Cells(i, 1)))+"-"
End If
Next i

' Create the array of unique values
UniqueArray = Split(mid(UniqueValues,1,len(UniqueValues)-1), "-")

' Write unique values to column "B"
Application.ScreenUpdating = False
For i = 1 To UBound(UniqueArray)
Cells(i, 2) = UniqueArray(i)
Next i
Application.ScreenUpdating = True

Debug.Print UBound(UniqueArray)     'Print down number of unique values in the immediate window
End Sub

Cons:


[1] As it relies on string processing, this method is not the fastest or the optimum for computer resources
[2] It may not work or it may get slower with large data sets having large number of unique values
[3] The delimiter should be carefully selected to make sure that no character will be processed as delimiter. For example, you can not use the "-" delimiter to get unique values of array of negative numbers.



Tags:


Excel VBA unique function

Excel VBA get unique strings

Excel VBA get unique values

Excel VBA get unique numbers

Excel VBA simplest unique function

Excel VBA shortest unique function

Excel VBA easiest unique function

Excel VBA how to get unique values

Excel VBA count unique values

Excel VBA number of unique values