Skip to content

Instantly share code, notes, and snippets.

@witwall
Last active February 26, 2016 03:56
Show Gist options
  • Save witwall/a48b87697e6d60881ab8 to your computer and use it in GitHub Desktop.
Save witwall/a48b87697e6d60881ab8 to your computer and use it in GitHub Desktop.
=(LEN(C3)-LEN(SUBSTITUTE(C3,B3,"")))/LEN(B3)
Function ArrayUnique(ByVal aArrayIn As Variant) As Variant
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ArrayUnique
' This function removes duplicated values from a single dimension array
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim aArrayOut() As Variant
Dim bFlag As Boolean
Dim vIn As Variant
Dim vOut As Variant
Dim i%, j%, k%
ReDim aArrayOut(LBound(aArrayIn) To UBound(aArrayIn))
i = LBound(aArrayIn)
j = i
For Each vIn In aArrayIn
For k = j To i - 1
If vIn = aArrayOut(k) Then bFlag = True: Exit For
Next
If Not bFlag Then aArrayOut(i) = vIn: i = i + 1
bFlag = False
Next
If i <> UBound(aArrayIn) Then ReDim Preserve aArrayOut(LBound(aArrayIn) To i - 1)
ArrayUnique = aArrayOut
End Function
Public Function Distinct(ByVal str As String, Optional delimiter As String = ",") As String
Dim aReturn As Variant
Dim aArray As Variant
aArray = Split(str, delimiter) ' Array(1, 2, 3, 1, 2, 3, "Test", "Test")
aReturn = ArrayUnique(aArray)
Distinct = Join(aReturn, delimiter)
End Function
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment