most common text within cells

[shakethingsup]

Find the 1st/2nd/3rd Common Text w/ Criteria

Hi all, Looking for a formula that will give me the result of the most common text within my dataset. I currently have, for the first common text result the following formula : {=INDEX(tblMain[Placement Provider],MODE(IF(tblMain[Reporting Fin-Year]=RIGHT(E4,5),IF(tblMain[Placement...

www.mrexcel.com

Top 10 most common TEXT

Hi all i want to be able to find the 10 top most common cells. I can find the top most common with this =INDEX($D$3:$D$35,MODE(IF($D$3:$D$35"",MATCH($D$3:$D$35,$D$3:$D$35,0)))) Second most common with this...

www.mrexcel.com

I don't think this is possible with just formulas but let's say I have 10 cells, and I want to find the top 5 most common words

Hello my name is bob

This is great, I wonder what I'm doing here

What are you doing bob?

Bob's my uncle

Bob's burger

now it's getting confusing

Is it bob or bob's

Job Bob

Bob jobs

Jim bob joe bob

So is there a formula in excel to identify the most common word - for example, bob, Bob's, Bob, I, is, etc. I'm okay with making the assumption that a " " = start of a new word/end of the previous one.

or is the easiest thing to do each time is to:
1. text to columns
2. but then I have many columns and then what's next?

 

[Peter_SSs]

Would you consider a macro instead?

This should list the top N plus ties.

Sub TopNMostCommon()
  Dim d As Object, AL As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
  
  Const TopN As Long = 5  '<- Edit as required
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set AL = CreateObject("System.Collections.ArrayList")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To TopN)
  For i = 1 To UBound(a)
    For Each itm In Split(a(i, 1))
      d(itm) = d(itm) + 1
    Next itm
  Next i
  For Each itm In d.Keys
    AL.Add Format(d(itm), "000000") & " " & itm
  Next itm
  AL.Add "000000 "
  AL.Sort
  AL.Reverse
  For i = 1 To TopN
    b(i) = AL.Item(i - 1)
  Next i
  Do Until i = AL.Count Or Split(AL.Item(i - 1))(0) < Split(AL.Item(i - 2))(0)
    ReDim Preserve b(1 To i)
    b(i) = AL.Item(i - 1)
    i = i + 1
  Loop
  With Range("B2").Resize(UBound(b))
    .Value = Application.Transpose(b)
    .TextToColumns DataType:=xlDelimited, Semicolon:=False, Comma:=False, Space:=True, Other:=False
    .Cells(0).Resize(, 2).Value = Array("Count", "Word")
  End With
End Sub

Results with your sample data & Top N set to 5

shakethingsup.xlsm
	A	B	C
1	Data	Count	Word
2	Hello my name is bob	6	bob
3	This is great, I wonder what I'm doing here	3	is
4	What are you doing bob?	3	Bob's
5	Bob's my uncle	2	what
6	Bob's burger	2	my
7	now it's getting confusing	2	doing
8	Is it bob or bob's
9	Job Bob
10	Bob jobs
11	Jim bob joe bob
Sheet1

 

[shakethingsup]

Would you consider a macro instead?

This should list the top N plus ties.

Sub TopNMostCommon()
  Dim d As Object, AL As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long
 
  Const TopN As Long = 5  '<- Edit as required
 
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set AL = CreateObject("System.Collections.ArrayList")
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To TopN)
  For i = 1 To UBound(a)
    For Each itm In Split(a(i, 1))
      d(itm) = d(itm) + 1
    Next itm
  Next i
  For Each itm In d.Keys
    AL.Add Format(d(itm), "000000") & " " & itm
  Next itm
  AL.Add "000000 "
  AL.Sort
  AL.Reverse
  For i = 1 To TopN
    b(i) = AL.Item(i - 1)
  Next i
  Do Until i = AL.Count Or Split(AL.Item(i - 1))(0) < Split(AL.Item(i - 2))(0)
    ReDim Preserve b(1 To i)
    b(i) = AL.Item(i - 1)
    i = i + 1
  Loop
  With Range("B2").Resize(UBound(b))
    .Value = Application.Transpose(b)
    .TextToColumns DataType:=xlDelimited, Semicolon:=False, Comma:=False, Space:=True, Other:=False
    .Cells(0).Resize(, 2).Value = Array("Count", "Word")
  End With
End Sub

Results with your sample data & Top N set to 5

shakethingsup.xlsm
	A	B	C
1	Data	Count	Word
2	Hello my name is bob	6	bob
3	This is great, I wonder what I'm doing here	3	is
4	What are you doing bob?	3	Bob's
5	Bob's my uncle	2	what
6	Bob's burger	2	my
7	now it's getting confusing	2	doing
8	Is it bob or bob's
9	Job Bob
10	Bob jobs
11	Jim bob joe bob
Sheet1

let me try this on Monday/Tuesday and get back to you! open to Macros, good to learn for me as well!