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

Get number of duplicates between any amount of rows

EDIT (Updated following solutions suggested)

I need to count the number of duplicates between two rows (in one formula) :

Row 1>  20  21  22  41    
Row 2>  3   20  30  22  47  49

Here the result should be 2 (20 & 22)

The two following formulas are working :

=COUNT(FILTER(UNIQUE(flatten(TRANSPOSE(A2:F3))), ARRAYFORMULA( COUNTIF(flatten(TRANSPOSE(A2:F3)),UNIQUE(flatten(TRANSPOSE(A2:F3))))>1 )))

and

=countif(query(flatten(A2:F), "Select count(Col1) group by Col1"), ">1")

But as soon as the two rows are not in the same sheet the formula doesn't work anymore (even with bracket)

Here is a Google Sheet with sample data and result : https://docs.google.com/spreadsheets/d/1PRNGgzAmpQUE0nCCuxBPJbM4gESUtnSMQf-cXG3ozBo/edit?usp=sharing

What do you think ?

Thank you !

Solution

  • EDIT (following OP's comment)

    Please use the following formula without a helper column

    =COUNT(FILTER(UNIQUE(flatten(TRANSPOSE(A2:F3))), ARRAYFORMULA( COUNTIF(flatten(TRANSPOSE(A2:F3)),UNIQUE(flatten(TRANSPOSE(A2:F3))))>1 )))

    Original reply

    You can use the following two formulas

    For the "helper" column

    =flatten(TRANSPOSE(A2:F3))

    For the count:

    =COUNTIF(ArrayFormula(if(H2:H="","",countifs(H2:H,H2:H,row(H2:H),"<="&row(H2:H)))),">1")

    enter image description here

    You can also incorporate them in a single formula by replacing H2:H with flatten(TRANSPOSE(A2:F3)) in the second formula