Function to get unique sorted words

Hi experts
I have in a cell some words separated by spaces
e.g
dog cat tree hair pepper she moon sun cat tree three pepper

I wanted a function which extracts the unique values and sorts the words by length and alphabetically

steps to get the result
first removed the duplicates
** dog cat tree hair pepper she moon sun three

second to sort the words by length

** pepper three tree hair moon dog cat

last step to sort each group of length alphabtically
** pepper three hair moon tree cat dog

Originally Posted by KingTamo

I wanted a function which extracts the unique values and sorts the words by length and alphabetically

As I yet wrote in your previous thread « the sort can be directly achieved within the Collection »
so according to the link I gave you in the same thread that's easy as this :

PHP Code: 

Function UniqueLengthSortedWords$(ByVal S$)
        Dim W, N&, V
    With New Collection
        For Each W In Split(S)
            W = Array(W, Len(W))
        For N = 1 To .Count
                V = .Item(N)
            If W(1) > V(1) Then
                .Add W, , N:  N = 0:  Exit For
            ElseIf W(0) <= V(0) And W(1) = V(1) Then
                If W(0) < V(0) Then .Add W, , N
                N = 0:  Exit For
            End If
        Next
            If N Then .Add W
        Next
        If .Count Then
            UniqueLengthSortedWords = .Item(1)(0)
            For N = 2 To .Count:  UniqueLengthSortedWords = UniqueLengthSortedWords & " " & .Item(N)(0):  Next
        End If
    End With
End Function

Sub DemoUniqueLengthSortedWords()
    Debug.Print UniqueLengthSortedWords("dog cat tree hair pepper she moon sun cat tree three pepper")
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Originally Posted by KingTamo

dog cat tree hair pepper she moon sun cat tree three pepper

** pepper three hair moon tree cat dog she sun

Function SortWords(ByVal txt As String) As String
    Dim e, i As Long
    With CreateObject("System.Collections.SortedList")
        For Each e In Split(Application.Trim(txt))
            .Item(Format$(1000 - Len(e), "000""" & e & """")) = e
        Next
        For i = 0 To .Count - 1
            SortWords = Trim$(SortWords & " " & .GetByIndex(i))
        Next
    End With
End Function

Just get rid of 1000 -

            .Item(Format$(Len(e), "000""" & e & """")) = e

Thanks a lot sir jindon
Your code is very excellent

@jindon
how can I change the code to make sort from the shortest word to the longest one?

Thanks a lot sir jindon and sir Marc