Count unique text in cell and range

[Akashwani]

Hi,

I've been banging my head for 3 hours with this problem, I need saving (again!)

I need to count the number of unique words that appear in a range, there are multiple words in some cells, this is what is causing me problems.
I can count the number of unique words in a range IF there is only one word per cell using the array formula in G3.
I need to count ALL unique words in A3:A10 and have the total (7) in C3.

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	*
2	List of Words	*	Unique count	*	List of Words	*	Count	*
3	Red	*	7	*	Red	*	7	*
4	Red Green Blue	*	*	*	Blue	*	*	*
5	Black White	*	*	*	Green	*	*	*
6	Black	*	*	*	White	*	*	*
7	White	*	*	*	Yellow	*	*	*
8	Blue	*	*	*	Pink	*	*	*
9	Yellow	*	*	*	Black	*	*	*
10	Yellow Pink	*	*	*	Red	*	*	*
11	*	*	*	*	Blue	*	*	*
12	*	*	*	*	Green	*	*	*
13	*	*	*	*	White	*	*	*
14	*	*	*	*	Yellow	*	*	*
15	*	*	*	*	Pink	*	*	*
16	*	*	*	*	Black	*	*	*
Sheet3

Your help is eagerly awaited

Thanks

Ak

 

[Akashwani]

Hi,

I have found the following VBA code that will help me resolve this problem, but I would prefer a formula approach as the Workbook must be VBA free

Sub CreateUniqueWords()
Dim LR As Long
Dim rngData As Range
Dim rngCell As Range
Dim colWords As Collection
Dim vntWord As Variant

Application.ScreenUpdating = False
Columns("J:J").ClearContents
On Error Resume Next
LR = Range("A" & Rows.Count).End(xlUp).Row
Set colWords = New Collection
Set rngData = Range("A3:A10" & LR)

For Each rngCell In rngData.Cells
    For Each vntWord In Split(Replace(Replace(Replace(rngCell.Value, """", ""), "]", ""), "[", ""), " ")
        colWords.Add colWords.Count + 1, vntWord
        If Cells(3 + colWords(vntWord), "L") <> 1 Then
            Cells(3 + colWords(vntWord), "J").Value = vntWord
            Cells(3 + colWords(vntWord), "K") = Cells(3 + colWords(vntWord), 10) + 1
            Cells(3 + colWords(vntWord), "L") = 1
        End If
    Next
    Columns("L:L").ClearContents
Next
    
LR = Range("J" & Rows.Count).End(xlUp).Row
Range("J3:J" & LR).Sort Cells(1, 2), xlDescending
Application.ScreenUpdating = True
End Sub

Any formula ideas?

Thanks

Ak

 

[Aladin Akyurek]

By the way, I bet the following...

{=SUM(IF(FREQUENCY(IF($E$3:$E$16<>"",MATCH($E$3:$E$16,$E$3:$E$16,0)),ROW($E$3:$E$16)-ROW($E$3)+1),1))}

would be faster than this:

{=SUM(IF(FREQUENCY(IF(LEN($E$3:$E$16)>0,MATCH($E$3:$E$16,$E$3:$E$16,0),""), IF(LEN($E$3:$E$16)>0,MATCH($E$3:$E$16,$E$3:$E$16,0),""))>0,1))}

 

[Akashwani]

Hi Aladin,

Thanks for the reply, yep, your formula does calculate faster.
Do you (or anyone else) have any suggestions on how I could solve the original question?

Thanks

Ak