Determine second most used text even if it only occurs once.

[cspengel]

There is always only going to be two different text strings. Both will never occur the same amount of times. Basically I just want to know which one occurs the least. The text may vary, so a countif or lookup is not an option as the names are not known, just the range of data. In example,

sam sam sam sam bill

least: bill

Thanks for any assistance.

 

[JGordon11]

Book1
	A	B	C	D	E	F	G	H	I	J
1	sam sam sam bill sam	bill		sam	sam	bill	sam		bill
2	bill sam sam	bill
3	bill bill sam sam bill	sam
4	george al al george al	george
5	al al george al george george george	al
6
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=LET(txt, A1:A5 & " ", lt, LEN(txt), f, TEXTBEFORE(txt," "), lf, LEN(f), s, TEXTBEFORE(SUBSTITUTE(txt, f & " ","")," "),ls, LEN(s), nf,(lt - LEN(SUBSTITUTE(txt, f & " ","")))/(LEN(f)+1), ns,(lt - LEN(SUBSTITUTE(txt, s & " ","")))/(LEN(s)+1), IF(nf>ns,s,f))
I1	I1	=LET(txt,TEXTJOIN(" ", 1, D1:G1) & " ", lt, LEN(txt), f, TEXTBEFORE(txt," "), lf, LEN(f), s, TEXTBEFORE(SUBSTITUTE(txt, f & " ","")," "),ls, LEN(s), nf,(lt - LEN(SUBSTITUTE(txt, f & " ","")))/(LEN(f)+1), ns,(lt - LEN(SUBSTITUTE(txt, s & " ","")))/(LEN(s)+1), IF(nf>ns,s,f))
Dynamic array formulas.

 

[Fluff]

Couple of other options.

Fluff.xlsm
	A	B	C	D	E	F	G
1
2	sam	sam	sam	bill	bill		bill
3	sam sam sam sam bill bill						bill
Data

Cell Formulas
Range	Formula
G2	G2	=LET(c,COUNTIF(A2:E2,A2:E2),TAKE(FILTER(A2:E2,c=MIN(c)),,1))
G3	G3	=LET(a,TEXTSPLIT(A3,," "),m,MMULT(--(a=TRANSPOSE(a)),SEQUENCE(ROWS(a),,,0)),TAKE(FILTER(a,m=MIN(m)),1))

 

[cspengel]

Thank you JGordon11 & Fluff! I went with the non-array, =LET(c,COUNTIF(A2:E2,A2:E2),TAKE(FILTER(A2:E2,c=MIN(c)),,1)) simply because I don't know how to add an array formula in VBA. Does just what I need.

​

​

 

[Fluff]

Glad we could help & thanks for the feedback.