Spreadsheet: 885293d1734771247-convert-a-mmult-to-byrow-lambda-playedbefore-countpairs-lambda-function-step-by-step-
Sheet: Step-by-Step

 |  | COUNTPAIRSλ:
 |  | =LAMBDA(field1,field2,[include],[total_only],
 |  | LET(
 |  | a, DROP(PIVOTBY(field1,field2,field1,ROWS,,0,,0,,include),,1),
 |  | u, TAKE(a,1),
 |  | x, IFERROR(--DROP(a,1),0), |  |  | Player A | Player B | Player C | Player D
 |  | m, [NAME]),""), |  | Group1 | [NAME] | Dieruf | [NAME] | Open
Change |  | IF( |  | Group2 | Pribble | Yaden | Wasson | Dietz
to |  | total_only, |  | Group3 | [NAME] | [NAME] | Barnes | Jacobs
SUM(m) | → → → | SUM(m)/2, |  | Group4 | Wachtel | Abell | Walsh | Ruffra
if |  | VSTACK(HSTACK("Pairs",u),HSTACK(TOCOL(u),m)) |  | Group5 | Blandford | Morehead | Hennessey | Nalley
desired |  | ) |  | Group6 | Clem | Garrett | O'Neil | Open
 |  | )
 |  | )
 |  | [include]: passed to the [filter_array] argument of PIVOTBY (optional)
 |  | [total_only]: TRUE or 1 to return the total pair count only; FALSE or 0 to return the summary of pair counts by player (default)
 | 1 |  |  | Group1 |  | True | Include "Open"
Step |  | =LET( |  |  | For | Past | Calculations
 | → → → | table, PlayedBefore, |  | [NAME] | Nalley | Scales | Open
2 | → → → | data,  DROP(table,1), |  | Dieruf | Ruffra | McFarland | Wick
3 | → → → | bcast, IFNA(SEQUENCE(ROWS(data)),data), |  | Yaden | O'Neil | Hagan | Wigley
4 | → → → | unpvt, UNIQUE(HSTACK(TOCOL(bcast),TOCOL(data))), |  | [NAME] | [NAME] | Prentice | [NAME]
5 | → → → | rowId, TAKE(unpvt,,1), |  | [NAME] | Clem | Nunn | Byrd
6 | → → → | names, DROP(unpvt,,1), |  | Nalley | Walsh | Dolwick | Jarvi
7 | → → → | incl,  EQUALSλ(names,XLOOKUP(E18,E7:E12,F7:I12))*IF(G18,1,names<>"Open"), |  | Garrett | Pribble | Byrnes | Woock
8 | → → → | p, PIVOTBY(rowId,names,[NAME],ROWS,,0,,0,,incl), |  | [NAME] | Blandford | Thompson | Cataldo
9 | → → → | a, DROP(p,,1), |  | Vogt | [NAME] | [NAME] | Epley
10 | → → → | u, TAKE(a,1), |  | Dietz | Scales | Abell | Usher
11 | → → → | x, IFERROR(--DROP(a,1),0), |  | Abell | [NAME] | Fackler | Pottinger
12 | → → → | n, MMULT(TRANSPOSE(x),x), |  | Scales | Woock | Prentice | [NAME]
13 | → → → | m, IF(u<>TOCOL(u),n,""), |  | Garrett | Jarvi | [NAME] | O'Neil
14 | → → → | VSTACK(HSTACK("Pairs",u),HSTACK(TOCOL(u),m)) |  | Wigley | Dietz | Walsh | Dieruf
 |  | ) |  | Barnes | [NAME] | Clem | Hennessey
 |  |  |  | Byrd | Ruffra | Byrnes | Vogt
 |  |  |  | Nalley | Pribble | Hagan | Yaden
 |  |  |  | Blandford | [NAME] | Nunn | Dolwick
 |  |  |  | Fackler | Thompson | [NAME] | Abell
 |  |  |  | Byrnes | [NAME] | Wick | [NAME]
 |  |  |  | [NAME] | Abell | Pottinger | Cataldo
 |  |  |  | McFarland | Epley | Usher | [NAME]
 |  |  |  | Hennessey | Wasson | Dieruf | Open
 |  |  |  | Yaden | [NAME] | [NAME] | Nalley
 |  |  |  | [NAME] | Vogt | Dietz | Abell
 |  |  |  | Barnes | Ruffra | O'Neil | [NAME]
 |  |  |  | Clem | Walsh | Pribble | Blandford
 |  |  |  | [NAME] | Scales | Byrnes | McFarland
 |  |  |  | Hagan | Prentice | Nunn | Dolwick
 |  |  |  | Byrnes | Thompson | [NAME] | Abell
 |  |  |  | Fackler | [NAME] | Wick | Wigley
 |  |  |  | [NAME] | Byrd | Jarvi | Woock
 |  |  |  | Cataldo | Epley | Usher | Pottinger
 |  |  |  | Wick | Byrd | Dolwick | Hennessey
 |  |  |  | Dieruf | O'Neil | Prentice | Wigley
 |  |  |  | Yaden | [NAME] | Nunn | Usher
 |  |  |  | [NAME] | Clem | Open | Open
 |  |  |  | Abell | Byrnes | [NAME] | Pottinger
 |  |  |  | Nalley | Pribble | Thompson | [NAME]
 |  |  |  | Vogt | [NAME] | Abell | Epley
 |  |  |  | Dietz | Open | Open | Open
 |  |  |  | Nunn | Yaden | Cataldo | Open
 |  |  |  | Walsh | Dietz | [NAME] | Vogt
 |  |  |  | Hagan | Woock | Pribble | Nalley
 |  |  |  | [NAME] | Ruffra | [NAME] | [NAME]
 |  |  |  | Dolwick | Usher | Abell | Epley
 |  |  |  | Byrnes | Jarvi | Wick | Fackler
 |  |  |  | [NAME] | Wasson | [NAME] | Abell
 |  |  |  | Wigley | Barnes | Hennessey | Clem
 |  |  |  | Thompson | Blandford | Pottinger | [NAME]
 |  |  |  | McFarland | Dieruf | Prentice | Byrd
 |  |  |  | Scales | Byrnes | O'Neil | [NAME]
 |  |  |  | Wick | Hennessey | Open | McFarland
 |  |  |  | Scales | Pottinger | Open | Fackler
 |  |  |  | Dieruf | Prentice | [NAME] | Nalley
 |  |  |  | Epley | Nunn | Blandford | Open
 |  |  |  | Pribble | [NAME] | Dolwick | Open
 |  |  |  | Walsh | Woock | Open | [NAME]
 |  |  |  | Clem | Thompson | Jarvi | Open
 |  |  |  | Byrd | [NAME] | Barnes | Open
 |  |  |  | Abell | O'Neil | [NAME] | Vogt
 |  |  |  | Ruffra | Open | Wigley | Open
 |  |  |  | [NAME] | Open | [NAME] | Byrnes
 |  |  |  | [NAME] | Dieruf | Pottinger | Open
 |  |  |  | Nalley | Nunn | Byrnes | Byrnes
 |  |  |  | Hermann | O'Neil | Blandford | Open
 |  |  |  | Barnes | Ruffra | McFarland | Open
 |  |  |  | Clem | Hagan | Thompson | Scales
 |  |  |  | Fackler | Woock | Wasson | [NAME]
 |  |  |  | Dolwick | [NAME] | [NAME] | Epley
 |  |  |  | Allgeier | Wigley | Byrd | [NAME]
 |  |  |  | Garrett | Abell | Jarvi | Prentice
 |  |  |  | [NAME] | O'Neil | McFarland | Open
 |  |  |  | Open | Jarvi | Pottinger | Open
 |  |  |  | Blandford | Ruffra | Garrett | Nalley
 |  |  |  | Nunn | Scales | [NAME] | Abell
 |  |  |  | Woock | [NAME] | Wick | Jacobs
 |  |  |  | Graven | Thompson | Dolwick | Byrnes
 |  |  |  | Dieruf | Byrnes | [NAME] | Hagan
 |  |  |  | Epley | Open | Byrd | Usher
 |  |  |  | Clem | Dietz | [NAME] | Open
 |  |  |  | Nunn | Ruffra | Hennessey | [NAME]
 |  |  |  | Pribble | Wick | Graven | Open
 |  |  |  | Barnes | Walsh | [NAME] | Hagan
 |  |  |  | Clem | [NAME] | Nalley | Jacobs
 |  |  |  | Wigley | O'Neil | Fackler | Open
 |  |  |  | Abell | Woock | Blandford | Kokinda
 |  |  |  | Open | Wasson | Dieruf | Prentice
 |  |  |  | Epley | Byrnes | Open | [NAME]
 |  |  |  | Pottinger | Dietz | Hermann | Allgeier
 |  |  |  | Thompson | Cataldo | Dolwick | Open
 |  |  |  | Garrett | Scales | McFarland | Usher
 |  |  |  | Wachtel | Yaden | [NAME] | [NAME]
 |  |  |  | [NAME] | McFarland | Kokinda | Wasson
 |  |  |  | Hennessey | Blandford | Ruffra | Yaden
 |  |  |  | Pribble | [NAME] | Cataldo | Wigley
 |  |  |  | Wachtel | [NAME] | Prentice | Prentice
 |  |  |  | Scales | [NAME] | O'Neil | Hagan
 |  |  |  | Walsh | Thompson | [NAME] | Nalley
 |  |  |  | Garrett | Wick | Open | [NAME]
 |  |  |  | Clem | Abell | Abell | Morehead
 |  |  |  | Nunn | Byrnes | Byrnes | [NAME]
 |  |  |  | Nunn | [NAME] | Byrnes | Byrnes
 |  |  |  | Ruffra | Jacobs | [NAME] | Fackler
 |  |  |  | Wasson | [NAME] | Allgeier | [NAME]
 |  |  |  | [NAME] | Epley | [NAME] | Kokinda
 |  |  |  | Nunn | Scales | Yaden | [NAME]
 |  |  |  | Clem | Wasson | [NAME] | Dieruf
 |  |  |  | [NAME] | Epley | Wick | Byrd
 |  |  |  | Jacobs | Garrett | Graven | Open
 |  |  |  | Pottinger | Fackler | [NAME] | Woock
 |  |  |  | Ruffra | Barnes | McFarland | Kokinda
 |  |  |  | Nunn | Scales | Yaden | [NAME]
 |  |  |  | Clem | Wasson | [NAME] | Dieruf
 |  |  |  | [NAME] | Epley | Wick | Byrd
 |  |  |  | Jacobs | Garrett | Graven | Open
 |  |  |  | Pottinger | Fackler | [NAME] | Woock
 |  |  |  | Ruffra | Barnes | McFarland | Kokinda
 |  |  |  | [NAME] | Wick | Hennessey | Open
 |  |  |  | Clem | Dietz | Nalley | Open
 |  |  |  | Nunn | Walsh | Yaden | Dolwick
 |  |  |  | Garrett | Scales | Wasson | [NAME]
 |  |  |  | [NAME] | Abell | Hermann | Open
 |  |  |  | Pottinger | O'Neil | McFarland | Byrnes
 |  |  |  | Thompson | [NAME] | Cataldo | [NAME]
 |  |  |  | Wachtel | Pribble | Barnes | Open
 |  |  |  | Ruffra | Wigley | Byrnes | [NAME]
 |  |  |  | Nunn | Yaden | [NAME] | Open
 |  |  |  | Clem | Graven | Hennessey | Wasson
 |  |  |  | Pottinger | Scales | Woock | Dieruf
 |  |  |  | Garrett | Blandford | [NAME] | Usher
 |  |  |  | Thompson | [NAME] | O'Neil | Allgeier
 |  |  |  | Epley | Wigley | Dolwick | Jacobs
 |  |  |  | Ruffra | Dietz | Cataldo | Nalley
 |  |  |  | [NAME] | Abell | Hermann | Prentice
 |  |  |  | Wachtel | Pribble | [NAME] | [NAME]
 |  |  |  | Byrd | Fackler | Byrnes | Hagan
 |  |  |  | [NAME] | Wick | McFarland | Open
 |  |  |  | Epley | Byrnes | Byrnes | Kokinda
 |  |  |  | Abell | Hermann | [NAME] | Hagan
 |  |  |  | Pribble | [NAME] | Ruffra | Morehead
 |  |  |  | Dolwick | Wasson | Cataldo | Open
 |  |  |  | Yaden | [NAME] | McFarland | [NAME]
 |  |  |  | Byrd | Graven | Dieruf | Barnes
 |  |  |  | Garrett | Nunn | Allgeier | Hennessey
 |  |  |  | Blandford | Scales | [NAME] | Prentice
 |  |  |  | Walsh | Wick | Nalley | Open
 |  |  |  | Pottinger | [NAME] | Ruffra | McFarland
 |  |  |  | Wick | Yaden | Walsh | Open
 |  |  |  | Clem | [NAME] | Cataldo | Morehead
 |  |  |  | Dolwick | Dietz | Garrett | Wasson
 |  |  |  | Kokinda | Abell | Graven | Hagan
 |  |  |  | Abell | Epley | O'Neil | Jacobs
 |  |  |  | Blandford | Wigley | [NAME] | Byrnes
 |  |  |  | Woock | [NAME] | [NAME] | Open
 |  |  |  | Usher | [NAME] | Pribble | Dieruf
 |  |  |  | Nalley | Wachtel | Hermann | Fackler
 |  |  |  | Byrnes | Wick | Hagan | [NAME]
 |  |  |  | Ruffra | Abell | Dolwick | Graven
 |  |  |  | Clem | [NAME] | [NAME] | Yaden
 |  |  |  | Hennessey | Garrett | [NAME] | Byrnes
 |  |  |  | McFarland | Cataldo | Blandford | Jacobs
 |  |  |  | Byrd | O'Neil | Wasson | Open
 |  |  |  | Epley | Fackler | Hermann | Dieruf
 |  |  |  | Abell | Pribble | Pottinger | Morehead
 |  |  |  | Pribble | Cataldo | Allgeier | Open
 |  |  |  | Wachtel | [NAME] | Hennessey | Dieruf
 |  |  |  | Clem | Blandford | O'Neil | Hermann
 |  |  |  | Garrett | Yaden | [NAME] | [NAME]
 |  |  |  | Wasson | Morehead | Jacobs | Open
 |  |  |  | Ruffra | [NAME] | Byrnes | Byrnes
 |  |  |  | McFarland | Nalley | Hagan | [NAME]
 |  |  |  | Nunn | Dietz | Barnes | Open
 |  |  |  | Byrd | Wick | [NAME] | Dolwick
 |  |  |  | Woock | Fackler | Graven | Kokinda
 |  |  |  | [NAME] | Graven | Wigley | Jacobs
 |  |  |  | Garrett | Pottinger | [NAME] | Nalley
 |  |  |  | Nunn | Pribble | Woock | O'Neil
 |  |  |  | Yaden | Cataldo | Barnes | Dieruf
 |  |  |  | Clem | Wick | [NAME] | Epley
 |  |  |  | Ruffra | Wasson | Hermann | Hagan
 |  |  |  | Blandford | Byrnes | [NAME] | Hennessey
 |  |  |  | [NAME] | Abell | Fackler | McFarland
 |  |  |  | Byrd | Scales | Byrnes | [NAME]
 |  |  |  | Open | Open | Open | Open
 |  |  |  | Blandford | Abell | McFarland | Open
 |  |  |  | Nunn | Morehead | [NAME] | Dieruf
 |  |  |  | Byrnes | Dolwick | Hermann | Open
 |  |  |  | Wachtel | Woock | O'Neil | Open
 |  |  |  | Ruffra | Scales | Wasson | Open
 |  |  |  | Garrett | Walsh | [NAME] | Cataldo
 |  |  |  | [NAME] | [NAME] | Fackler | [NAME]
 |  |  |  | Abell | Wigley | Nalley | [NAME]
 |  |  |  | Clem | Graven | [NAME] | [NAME]
 |  |  |  | Epley | Pribble | Byrnes | Hennessey
 |  |  |  | [NAME] | [NAME] | Ruffra | Yaden
 |  |  |  | Blandford | Woock | [NAME] | Kokinda
 |  |  |  | Pribble | Walsh | Hennessey | [NAME]
 |  |  |  | Epley | Dieruf | Barnes | Open
 |  |  |  | Wachtel | Nunn | O'Neil | Jacobs
 |  |  |  | [NAME] | Cataldo | Prentice | Open
 |  |  |  | Pottinger | [NAME] | Abell | Prentice
 |  |  |  | Garrett | [NAME] | Hermann | Usher
 |  |  |  | Clem | Scales | Morehead | [NAME]
 |  |  |  | Dietz | McFarland | Dolwick | Open
 |  |  |  | [NAME] | Garrett | Pribble | Cataldo
 |  |  |  | Clem | Blandford | Wasson | Dieruf
 |  |  |  | Ruffra | McFarland | Kokinda | Barnes
 |  |  |  | Dolwick | Hennessey | Prentice | [NAME]
 |  |  |  | Byrnes | Hermann | [NAME] | [NAME]
 |  |  |  | Wick | Morehead | [NAME] | Open
 |  |  |  | Nalley | [NAME] | Ruffra | [NAME]
 |  |  |  | Scales | [NAME] | Byrnes | Prentice
 |  |  |  | McFarland | Prentice | Fackler | [NAME]
 |  |  |  | Wasson | Kokinda | Graven | Cataldo
 |  |  |  | Epley | Pottinger | Byrnes | Open
 |  |  |  | [NAME] | Abell | Wasson | [NAME]
 |  |  |  | Fackler | Prentice | [NAME] | Nalley
 |  |  |  | Nunn | Pribble | [NAME] | Kokinda
 |  |  |  | Ruffra | Graven | Allgeier | Prentice
 |  |  |  | Wick | Abell | Cataldo | Woock
 |  |  |  | Dietz | Hennessey | Jacobs | [NAME]
 |  |  |  | Epley | Morehead | [NAME] | [NAME]
 |  |  |  | [NAME] | Blandford | Pribble | Cataldo
 |  |  |  | Jarvi | Byrnes | Hermann | Wachtel
 |  |  |  | O'Neil | Nalley | Wick | Dolwick
 |  |  |  | Hagan | [NAME] | Nunn | Dietz
 |  |  |  | Woock | Garrett | Epley | [NAME]
 |  |  |  | Scales | Byrnes | Prentice | Prentice
 |  |  |  | Clem | [NAME] | Ruffra | Prentice
 |  |  |  | [NAME] | Jarvi | Wasson | Blandford
 |  |  |  | Scales | Byrnes | Hermann | Wick
 |  |  |  | Allgeier | [NAME] | [NAME] | Nunn
 |  |  |  | Garrett | Abell | Abell | Dieruf
 |  |  |  | Walsh | Byrnes | Dolwick | Jacobs
 |  |  |  | Epley | [NAME] | Nalley | Prentice
 |  |  |  | Jacobs | Pribble | Garrett | Open
 |  |  |  | [NAME] | Hennessey | Abell | [NAME]
 |  |  |  | Yaden | O'Neil | Wasson | Nalley
 |  |  |  | Dieruf | Blandford | Barnes | Ruffra
 |  |  |  | Clem | Walsh | Dietz | [NAME]
 |  |  |  | Morehead | [NAME] | [NAME] | Wachtel
 |  |  |  | Jacobs | [NAME] | Wachtel | Open
 |  |  |  | Hennessey | [NAME] | Abell | Clem
 |  |  |  | Wasson | Nalley | Barnes | Walsh
 |  |  |  | Dieruf | Garrett | Ruffra | Pribble
 |  |  |  | Morehead | [NAME] | O'Neil | Blandford
 |  |  |  | Yaden | Dietz | [NAME] | [NAME]
 |  |  |  | Hennessey | Wasson | Jacobs | Open
 |  |  |  | Yaden | [NAME] | Morehead | Dieruf
 |  |  |  | Garrett | [NAME] | Dietz | [NAME]
 |  |  |  | Wachtel | Abell | Ruffra | Barnes
 |  |  |  | Clem | Walsh | O'Neil | [NAME]
 |  |  |  | Pribble | Blandford | Scales | [NAME]
 |  |  |  | [NAME] | Hermann | Byrnes | Prentice
 |  |  |  | Nunn | McFarland | Hagan | Prentice
 |  |  |  | Thompson | Allgeier | Byrnes | [NAME]
 |  |  |  | [NAME] | Abell | Wick | Cataldo
 |  |  |  | Jacobs | [NAME] | Wachtel | Open
 |  |  |  | Hennessey | [NAME] | Abell | Clem
 |  |  |  | Wasson | Nalley | Barnes | Walsh
 |  |  |  | Dieruf | Garrett | Ruffra | Pribble
 |  |  |  | Morehead | [NAME] | O'Neil | Blandford
 |  |  |  | Yaden | Dietz | [NAME] | [NAME]