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:
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
Cons:
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
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.
[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
No comments:
Post a Comment