1

Let's say I have a column of values, each of which is a string.

A
This is a string
This is longer than that
My favorite is short
But sometimes short is longer
How is a short longer

I would like to extract all of the unique two-word combinations and count the number of times they occur without regard to capitalization.

For example, the data above would return:

Pair Count                                                                                      
This is 2
is a 2
a string 1
is longer 2
longer than 1
than that 1
My favorite 1
favorite is 1
is short 1
But sometimes 1
sometimes short 1
short is 1
How is 1
a short 1
short longer 1

I've tried split/join combinations, but I can't figure out how to sequence the discovery of each pair.

2 Answers 2

0

See test sheet.

Unique pairs of 2 word from column A are in column D as a result of following formula:

=ARRAYFORMULA(
 UNIQUE(
  FILTER(
  {"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}&" "&{TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "));"#"},
  NOT(REGEXMATCH({"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}&" "&{TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "));"#"},".*#")))))

Idea is make column of single words and concatenate is with the same column offset by 1 row.

Following part returns column of single words.

TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))

Then we shift it down, please note that I used "#" symbol for shifting purposes and further filter and if original text contains "#" it won't be included in final result, so you can change that symbol if you need.

{"#";TRANSPOSE(SPLIT(CONCATENATE(A2:A&" ")," "))}

concatenate with other column and filter rows that would contain only one word.

0
Using QUERY

Order By can be added to the QUERY

=QUERY(
  TOCOL(BYROW(A2:A6, LAMBDA(r, 
    TOROW(LET(txt,SPLIT(LOWER(r)," "),
    MAP(SEQUENCE(COUNTA(txt)-1), LAMBDA(s,
      JOIN(" ",CHOOSECOLS(txt,s,s+1)))))))),1),
  "Select Col1, COUNT(Col1) Group by Col1 Label COUNT(Col1) ''")

QUERY Version

Using MAP

The formula can be wrapped in SORT

=LET(
  pairs,TOCOL(BYROW(A2:A, LAMBDA(r, 
    TOROW(LET(txt,SPLIT(LOWER(r)," "),
    MAP(SEQUENCE(COUNTA(txt)-1), LAMBDA(s,
      IFERROR(JOIN(" ",CHOOSECOLS(txt,s,s+1))))))))),1),
  MAP(UNIQUE(pairs), LAMBDA(u, {u, COUNTIF(pairs,u)})))

MAP Version

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.