0

Please see: Extracting and counting unique word frequency from a range

In that question the asker was seeking unique single words. I'm trying to accomplish the same but finding every unique pair of words. If a cell doesn't have two words, then it doesn't have any entries. If a cell has 3 words then it would have two combinations A + B and B + C

I've tried to parse with splits and substitute pipes for spaces by using the len(cell) - len(substitute(cell," ","")) which gives me the number of words, but that doesn't work either.

Marston Gould
  • 1

1 Answers1

1

Try this (assuming your words are in Column A, put this in cell B1):

=index(
query(
query(
trim(iferror(flatten(split(
regexreplace(regexreplace(lower(A:A),"[^A-Za-z\ \']+",""),"([\w\']+\ [\w\']+)","$1,")
&","&
regexreplace(regexreplace(lower(if(len(A:A)=len(substitute(A:A," ",""))+1,,A:A)),"[^A-Za-z\ \']+",""),"\w*\ ([\w\']+\ [\w\']+)","$1,")
,",",1,1)),)),
"where Col1 like '% %' order by Col1",1),
"select Col1, count(Col1) group by Col1 label Col1 'Word pairs', count(Col1) 'Qty'",0)
)

It's quite involved, but I'll break it down if it works for you!

Aresvik
  • 4,484
  • 1
  • 5
  • 18