Forum Discussion
brunomerola
Nov 19, 2022Brass Contributor
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
Sort By
- dscheikeyBronze Contributor
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.
- brunomerolaBrass ContributorThank 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?- brunomerolaBrass Contributor
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))) ) ))