Forum Discussion
Count exact matches in multiple columns
What would be the most efficient way using dynamic arrays or lambda to count the numbers of exact matches in each row of a dataset?
Expected outcome:
| #1 | #2 | #3 | #4 | # of matches |
| A | A | A | 3 (#1 and #2, #1 and #3, #2 and #3) | |
| A | A | B | 1 | |
| A | B | A | 1 | |
| A | B | C | 0 | |
| A | A | A | A | 6 |
| A | A | B | B | 2 |
| A | B | B | C | 1 |
| A | B | C | D | 0 |
4 Replies
If your real data does not have very many columns, then you can stay simple and use the following formula:
=SUM(COUNTIF(B1:D1,A1),COUNTIF(C1:D1,B1),COUNTIF(D1:D1,C1))Then copy it down.
- Thank you, it works perfectly, but I'll have dozens of columns and hundreds of rows.
How would this be converted to a one single array formula?With a little help from combinatorics, I think I found it:
=BYROW(array,LAMBDA(row, LET( unique,UNIQUE(TOROW(row,1,1),1), freq,COUNTIF(row,unique), SUM(IF(freq>1,COMBIN(freq,2))) ) ))
