Forum Discussion

Brass Contributor

Nov 19, 2022

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 ...

excel

Formulas and Functions

office 365

brunomerola

Brass Contributor

Nov 19, 2022

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?

brunomerola

Brass Contributor

Nov 19, 2022

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)))
    )
))

dscheikey
Bronze Contributor
Nov 19, 2022
brunomerola
=BYROW(A1:D8,LAMBDA(in,LET(co,SEQUENCE(1,COLUMNS(in)-1),SUM(COUNTIF(DROP(in,,co),CHOOSECOLS(in,co))))))
Mine was something like this 🙂