Ok guys I had to resource to VBA and but it works fine now:
If you find a better solution not using VBA I'd like to use that
Here's the function:
Function TOCOLUMNS(SearchRange As Range)
Dim v As Variant ' Multi-purpose array
Dim c As Variant ' Element of array (corresponds to cell)
Dim d As Object ' Scripting.Dictionary object
Dim i As Long ' Loop index for bubble sort
Dim j As Long ' Idem
Dim t As Variant ' Placeholder for sort
' Create a Dictionary object
Set d = CreateObject("Scripting.Dictionary")
' Store values of SearchRange in a two-dimensional array
v = SearchRange.Value
' Loop through the array elements (cells)
For Each c In v
' Only use non-blank values
If c <> "" Then
' Set the dictionary entry with key c to 1
' If that entry doesn't exist yet, it will be created
' If it already existed, nothing will change
' This way, we collect the unique values
d(c) = 1
End If
Next c
' Set v to the array of keys of the dictionary
' The array contains the unique values
v = d.Keys
' Use the simple bubble sort algorithm to sort the array
For i = LBound(v) To UBound(v) - 1
For j = i + 1 To UBound(v)
' If an element is larger than a later element...
If v(i) > v(j) Then
' ... then swap the elements
t = v(i)
v(i) = v(j)
v(j) = t
End If
Next j
Next i
' v is now an array laid out in a row
' Return the array transposed to a column
TOCOLUMNS = Application.Transpose(v)
End Function
