In Microsoft Office Excel 365, there is a function name "UNIQUE". Now the best feature of this function is it extract the unique set of data from a List.
It works perfectly, when the data is in rows, However if the data is in columns, in that case, it can not extract the Unique Data.
We can see in this image that Unique function cannot extract unique data when the data is in columns.
To tackle this situation, we can use a VBA UDF (user defined function) which can extract unique data from multiple columns and transpose the data vertically.
So here we will create a Function called "smart_uniques"
Function smart_uniques(rng As Range) As Variant()
Dim list As New Collection
Dim Ulist() As Variant
On Error Resume Next
For Each value In rng
list.Add CStr(value), CStr(value)
Next
On Error GoTo 0
Debug.Print list.Count
ReDim Ulist(list.Count - 1, 0)
For i = 0 To list.Count - 1
Ulist(i, 0) = list(i + 1)
Next
smart_uniques = Ulist
End Function
Function RetArray() As Variant()
Dim arr() As Variant
ReDim arr(2, 0)
arr(0, 0) = "sdfd"
arr(1, 0) = "sdfd"
arr(2, 0) = 2
smart_uniques = arr
End Function
Lets see , how this formula works.
So, we have to apply below formula:
=smart_uniques(A1:C1)
Here A1 to C1 is my data range and we can notice that the unique data is returned vertically in Cell "A3"
This function can work on multiple rows and columns.