Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

Formula for Counting recurring "Pairs"

In the following example what is the best way to go about evaluating the 'best team' aka the pair that keep recurring?

| Winner In Event A     | Winner In Event B     |
|-------------------    |-------------------    |
| Bob                   | Alfred                |
| Bob                   | Dave                  |
| Bob                   | Alfred                |
| Jason                 | Alfred                |
| John                  | Fred                  |
| Fred                  | John                  |
| John                  | Fred                  |
| Richard               | Jason                 |
| Richard               | Bob                   |

In this case, I would expect it to choose Fred & John as they are paired 3 times and not be tied with Bob & Alfred who are only paired 2 times.

On the example above, I include the standard VLookup which I'm sure holds the key to the solution, but I'm not sure what the paired version looks like

Solution

  • without 3rd column:

    =INDEX(ARRAYFORMULA(QUERY(IF(LEN(A1:A&B1:B), 
 IF(A1:A>B1:B, A1:A&" & "&B1:B, B1:B&" & "&A1:A), ), 
 "select Col1,count(Col1) group by Col1 order by count(Col1) desc", 0)), 2, 1)

    0

    full table:

    =ARRAYFORMULA(QUERY(IF(LEN(A1:A&B1:B), 
 IF(A1:A>B1:B, A1:A&" & "&B1:B, B1:B&" & "&A1:A), ), 
 "select Col1,count(Col1) 
  where Col1 is not null
  group by Col1 
  order by count(Col1) desc
  label count(Col1)''", 0))

    0