Formula for > 1. Listing all unique Words in a Cell Range alphabetically, then > 2. Listing the Total Count for each Word

[DataQuestioner]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]CELLS WITH TEXT TO BE COUNTED[/TD]
[TD="align: center"]Alphabetical WORD LIST from Column 'A' CELLS[/TD]
[TD="align: center"]WORD COUNT from Column 'A'[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Test text in here[/TD]
[TD="align: center"]Even[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]More test text here[/TD]
[TD="align: center"]Final[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Even more text here[/TD]
[TD="align: center"]here[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Final test text here[/TD]
[TD="align: center"]in[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]more[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]More[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]test[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Test[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"]text[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

This particular Formula Array request may take some explaining...so please be patient with me as I try to detail the problem. If my explanation is not definitive enough then please ask for clarification. OK, here we go >

1. Cells A2:A5 (this could be an range running up to 1000s of cells in the 'A' column) contains the Text that needs to be searched (each cell could contain up to 100 words that will exclusively contain letters - no numbers, symbols or punctuation).
2. I'm looking for a Formula that can search all of the Words in the 'A' column Range, and then list each unique word (case sensitive) as shown in the 'B' column, in alphabetical order.
3. The 'C' column will provide the Word Count for each Word listed in column 'B'.

NOTE: I have tried taking the Words in column 'A' and using the "Data/Text to Columns" command to put each word occurrence in a different cell, and then using the "Filter" command to list each column in alphabetical order, and finally using the "=COUNT" command to total the "Filter" list, but this is too cumbersome and time consuming.

There must be a more efficient way of doing this. Thanks.

 

[DataQuestioner]

BUMP

I need someone to make some improvements to Hiker95's code in Post #25 - it's returning some bugs that I need removed.

 

[stewartjack990]

Good Day

This output is exactly what i need also. I am using Excel 2010. I am not familiar with VB but am willing to give it a shot.

I followed the instructions above. When i try to run this macro, i am getting "Run-Time error '13' Type Mismatch

In Debub, the error statement is highlighted below.:

a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
For i = LBound(a, 1) To UBound(a, 1)
If InStr(Trim(a(i, 1)), " ") = 0 Then
d(a(i, 1)) = 1
ElseIf InStr(Trim(a(i, 1)), " ") > 0 Then

Any ideas? All of my date is in column A of the worksheet

Many thanks