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

 

[JEC]

Can you provide desired outcome of this dataset?

 

[Steve_Smith_1123]

Like this:

French, Topbox, Timed

 

[JEC]

Those words occur just once..

 

[Steve_Smith_1123]

I was just giving an example of how the data set would look. That's what you wanted right?

 

[JEC]

Only for office 365

Book1
	A	B	C
1	Data		Output
2	French Calls Getting Closed Message		French
3	New Queue FRL_AIG UL/ROP/GP95. - AIG - 5		Calls
4	RoadsideAssistance_Bot_prod Lex Tuning - EN-23
5	TopBox metadata in CTR - TRN-92
6	Agency Sales Mailbox VM coming in as Raw Text
7	Timed ACW does not appear to be working on some agents
8	AWS Connect IVR enhancements for Customer Support
9	798720: Post Go-Live Report Attributes: PI2 Priority 2 of 7
10	RoadsideAssistance_Bot_prod Intent expansion - NewReservation_prod
11	French toast
12	blabla TopBox
13	as;ldkfjasl;k
14	adskfja;sdf
15	Report like
16	the calls are getting boring
17	boring calls
18	French cheese
19	its timed well
20	blabla timed
21	french timed toast
22	TopBox is fine
23	Report
24	Calls
25	Calls
26	French
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=LET(r,REDUCE("",A2:A26,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,," ")))),UNIQUE(FILTER(r,(LEN(r)>1)*BYROW(r,LAMBDA(x,COUNTA(FILTER(r,r=x))))>4)))
Dynamic array formulas.

 

[JEC]

Or

=LET(r,TEXTSPLIT(TEXTJOIN(" ",,A2:A26),," "),UNIQUE(FILTER(r,(LEN(r)>1)*BYROW(r,LAMBDA(x,COUNTA(FILTER(r,r=x))))>4)))

 

[Peter_SSs]

Or

=LET(r,TEXTSPLIT(TEXTJOIN(" ",,A2:A26),," "),UNIQUE(FILTER(r,(LEN(r)>1)*BYROW(r,LAMBDA(x,COUNTA(FILTER(r,r=x))))>4)))

That one would almost certainly fail due to the length limit of TEXTJOIN given the OP's data size

The data set consists of over 4,000 lines.

The first formula took almost a minute to calculate on my machine, given 4,000 rows like your sample repeated.

@Steve_Smith_1123
Could you consider a user-defined function like below (though I believe that it will not work in your MacOS environment)?

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
  
  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))
      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

Steve_Smith_1123.xlsm
	A	B
1	Data
2	French Calls Getting Closed Message	French
3	New Queue FRL_AIG UL/ROP/GP95. - AIG - 5	Calls
4	RoadsideAssistance_Bot_prod Lex Tuning - EN-23
5	TopBox metadata in CTR - TRN-92
6	Agency Sales Mailbox VM coming in as Raw Text
7	Timed ACW does not appear to be working on some agents
8	AWS Connect IVR enhancements for Customer Support
9	798720: Post Go-Live Report Attributes: PI2 Priority 2 of 7
10	RoadsideAssistance_Bot_prod Intent expansion - NewReservation_prod
11	French toast
12	blabla TopBox
13	as;ldkfjasl;k
14	adskfja;sdf
15	Report like
16	the calls are getting boring
17	boring calls
18	French cheese
19	its timed well
20	blabla timed
21	french timed toast
22	TopBox is fine
23	Report
24	Calls
25	Calls
26	French
Sheet3

Cell Formulas
Range	Formula
B2:B3	B2	=WordList(A2:A26,5)
Dynamic array formulas.

The above function replicates @JEC's formula in that it does not report 1-letters "words" (like "-" in your data).
It also has this flexibility requested.

occur more than a certain number of times.

Tested on the same 4,000 row data as above, this UDF took about 0.04 seconds.

 

[JEC]

Dictionary indeed not working on Macs.

 

[Peter_SSs]

Dictionary indeed not working on Macs.

OP also has Windows platform so hopefully they could use that.