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

DataQuestioner

Board Regular
Joined
Sep 12, 2013
Messages
115
ABC
1CELLS WITH TEXT TO BE COUNTEDAlphabetical WORD LIST from Column 'A' CELLSWORD COUNT from Column 'A'
2Test text in hereEven1
3More test text hereFinal1
4Even more text herehere4
5Final test text herein1
6more1
7More1
8test2
10Test1
11text4

<tbody>
</tbody>

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.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Have you considered a VBA (macro) solution?
 
Upvote 0
JoeMo - thanks for your reply.

I'm open to any suggestions that will reduce the amount of steps, or repetition, in this problem.

Is the method that I've described in my NOTE a sensible one, JoeMo? Or is there a better way?
 
Upvote 0
DataQuestioner,

Sample raw data before the macro:


Excel 2007
ABC
1CELLS WITH TEXT TO BE COUNTEDAlphabetical WORD LIST from Column 'A' CELLSWORD COUNT from Column 'A'
2Test text in hereEven
3More test text hereFinal
4Even more text herehere
5Final test text herein
6more
7More
8test
9Test
10text
11
Sheet1


After the macro using three arrays in memory:


Excel 2007
ABC
1CELLS WITH TEXT TO BE COUNTEDAlphabetical WORD LIST from Column 'A' CELLSWORD COUNT from Column 'A'
2Test text in hereEven1
3More test text hereFinal1
4Even more text herehere4
5Final test text herein1
6more1
7More1
8test2
9Test1
10text4
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub GetWordCount()
' hiker95, 10/27/2013
' http://www.mrexcel.com/forum/excel-questions/735360-formula-1-listing-all-unique-words-cell-range-alphabetically-then-2-listing-total-count-each-word.html
Dim a As Variant, b As Variant, s
Dim i As Long, ii As Long, iii As Long, n As Long
a = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
b = Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row)
For ii = 1 To UBound(b, 1)
  n = 0
  For i = 1 To UBound(a, 1)
    s = Split(Trim(a(i, 1)), " ")
    For iii = LBound(s) To UBound(s)
      If Trim(b(ii, 1)) = s(iii) Then n = n + 1
    Next iii
  Next i
  b(ii, 2) = n
Next ii
Range("B2").Resize(UBound(b, 1), UBound(b, 2)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetWordCount macro.
 
Upvote 0
Hiker95,
The way I read the OP, column B is empty initially so the macro must extract all unique words from column A and enter them alphabetized in column B then provide a count in column C of the number of instances each unique word in column A.

"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."

Based on this, the "raw data" would have column B empty or am I misinterpreting the OP?
 
Upvote 0
JoeMo,

Nice catch.

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.

I missed this part????

Back to the drawing board.
 
Upvote 0
JoeMo,

Thank you very much.


DataQuestioner,

Sample raw data:


Excel 2007
ABC
1CELLS WITH TEXT TO BE COUNTEDAlphabetical WORD LIST from Column 'A' CELLSWORD COUNT from Column 'A'
2Test text in here
3More test text here
4Even more text here
5Final test text here
6
7
8
9
10
11
Sheet1


After the new macro:


Excel 2007
ABC
1CELLS WITH TEXT TO BE COUNTEDAlphabetical WORD LIST from Column 'A' CELLSWORD COUNT from Column 'A'
2Test text in hereEven1
3More test text hereFinal1
4Even more text herehere4
5Final test text herein1
6More1
7more1
8Test1
9test2
10text4
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Option Explicit
Sub GetWordCountV2()
' hiker95, 10/27/2013
' http://www.mrexcel.com/forum/excel-questions/735360-formula-1-listing-all-unique-words-cell-range-alphabetically-then-2-listing-total-count-each-word.html
Dim d As Object
Dim a As Variant, b As Variant, s
Dim i As Long, ii As Long, iii As Long, n As Long, lr As Long
Set d = CreateObject("Scripting.Dictionary")
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
    s = Split(a(i, 1), " ")
    For iii = LBound(s) To UBound(s)
      d(s(iii)) = 1
    Next iii
  End If
Next i
Range("B2").Resize(d.Count) = Application.Transpose(d.Keys)
lr = Cells(Rows.Count, 2).End(xlUp).Row
With Range("B2:B" & lr)
  .Sort key1:=Range("B2"), order1:=1
  .HorizontalAlignment = xlCenter
End With
b = Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row)
For ii = 1 To UBound(b, 1)
  n = 0
  For i = 1 To UBound(a, 1)
    s = Split(Trim(a(i, 1)), " ")
    For iii = LBound(s) To UBound(s)
      If Trim(b(ii, 1)) = s(iii) Then n = n + 1
    Next iii
  Next i
  b(ii, 2) = n
Next ii
Range("B2").Resize(UBound(b, 1), UBound(b, 2)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetWordCountV2 macro.
 
Upvote 0
Hiker95,
The way I read the OP, column B is empty initially so the macro must extract all unique words from column A and enter them alphabetized in column B then provide a count in column C of the number of instances each unique word in column A.

"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."

Based on this, the "raw data" would have column B empty or am I misinterpreting the OP?


JoeMo - you are correct - the only data available for this problem is in Column 'A'.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top