Excel Formula - Comma Separated List of all Words Occurring over 5 times in a column

[Steve_Smith_1123]

Hello,
Hope you're all doing well. I think it is more because I am not phrasing the question the right way, but I can't find a way to get a list of all string values (case agnostic) that occur more than a certain number of times. The data set consists of over 4,000 lines.

Thanks in advance for any help you can provide

What are you looking for exactly Steve?:

1. A list of values, either in comma separated or in a range.
2. All string values that occur in the column more than four times (case agnostic).

Can you provide a sample of how the data looks?:

French Calls Getting Closed Message

New Queue FRL_AIG UL/ROP/GP95. - AIG - 5

RoadsideAssistance_Bot_prod Lex Tuning - EN-23

TopBox metadata in CTR - TRN-92

Agency Sales Mailbox VM coming in as Raw Text

Timed ACW does not appear to be working on some agents

AWS Connect IVR enhancements for Customer Support

798720: Post Go-Live Report Attributes: PI2 Priority 2 of 7

RoadsideAssistance_Bot_prod Intent expansion - NewReservation_prod

 

[Steve_Smith_1123]

That worked great. Is there a way we can add a filter that would allow me to ignore certain strings? Even if its just a matter of adding the strings to be ignored in the code, I can do that.
The reason for this is because sometimes there are words that I want to ignore, like, "in", but there are words that I don't want to ignore like, "S3". There is no hard and fast rule that I can lay down regarding which words to ignore, and which ones not to.

 

[Steve_Smith_1123]

I'm not sure where you got the idea that I have a mac. Definitely a P.C. guy.

 

[Fluff]

I'm not sure where you got the idea that I have a mac

It says it in your profile.

 

[Peter_SSs]

I'm not sure where you got the idea that I have a mac. Definitely a P.C. guy.

As @Fluff says, it couldn't be much clearer.

Is there a way we can add a filter that would allow me to ignore certain strings? Even if its just a matter of adding the strings to be ignored in the code,

Try this

Function WordList(rng As Range, MinCount As Long) As Variant
  Dim d As Object
  Dim a As Variant, itm As Variant
  Dim i As Long
  
  Const IgnoreList As String = " in to be the are " '<- Note space at beginning and end as well as between words
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = rng.Value
  For i = 1 To UBound(a)
    For Each itm In Split(a(i, 1))
      If InStr(1, IgnoreList, " " & itm & " ", vbTextCompare) = 0 Then d(itm) = d(itm) + 1
    Next itm
  Next i
  For Each itm In d.Keys
    If d(itm) < MinCount Or Len(itm) < 2 Then d.Remove itm
  Next itm
  WordList = Application.Transpose(d.Keys)
End Function