Function to get unique sorted words

[KingTamo]

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

[Dante Amor]

Use this UDF like:

=get_unique_sorted(A2)

Please Login or Register  to view this content.

[Marc L]

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 :

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 » ! ◄ ◄

[jindon]

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

** pepper three hair moon tree cat dog she sun

Please Login or Register  to view this content.

[KingTamo]

Thanks a lot sir jindon
Your code is very excellent

[KingTamo]

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

[jindon]

Just get rid of 1000 -

Please Login or Register  to view this content.

[KingTamo]

Thanks a lot sir jindon and sir Marc