Extract only unique words from selected sentences

7absinth · Nov 3rd 2015

I have a column of values say :

I am happy and sad
I am extremely happy
I am sad
I am very sad
I am very very sad

Excel have to automatically identify and delete the words that have been repeated. In this case, after running macro I should be left with only :

and
Extremely

because all other words are repetitive. i don't have a list of words, so Excel should automatically gives us only Unique words which haven't been used more than once.

Does this macro is practicable?

Thank you.

jindon · Nov 3rd 2015

Re: Extract only unique words from selected sentences

Code

Sub test()
    Dim r As Range, e, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    For Each r In Selection
        If r.Value <> "" Then
            For Each e In Split(r.Value)
                If Not dic.exists(e) Then
                    dic(e) = True
                Else
                    dic(e) = False
                End If
            Next
        End If
    Next
    For Each e In dic
        If Not dic(e) Then dic.Remove e
    Next
    If dic.Count Then
        MsgBox Join(dic.keys, vbLf)
    Else
        MsgBox "No word"
    End If
End Sub

Display More

7absinth · Nov 4th 2015

Re: Extract only unique words from selected sentences

Works great. Cant believe it. Thank you. You are Excel VBA Superguru.

7absinth · Nov 7th 2015

Re: Extract only unique words from selected sentences

Code works well, but my word list is very long. Pop-screen is out of window screen. How to put this list in the cells?

jindon · Nov 7th 2015

Re: Extract only unique words from selected sentences

Change

Code

If dic.Count Then 
        MsgBox Join(dic.keys, vbLf) 
    Else

to

Code

If dic.Count Then 
        [a1].Resize(dic.Count).Value = Application.Transpose(dic.keys) 
    Else

Alter [a1] to suite

Extract only unique words from selected sentences

Participate now!

Share