Get number of duplicates between any amount of rows

1
votes

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 !

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
For documentation purposes if you can, please accept the answer (✓) that's been helpful to you - it helps other people that have the same issue in the future find the solution too :) – Rafa Guillermo
Your COUNTIF probably didn't work because you'd need to transpose those values first, e.g.: =SUMPRODUCT(COUNTIF(1:1,TRANSPOSE(2:2))). Also, since it's an array formula you'd either need ARRAYFORMULA or SUMPRODUCT. Also, if an answer below suited your needs, consider to upvote/accept it as such. Off-topic, you might want to revisit most of your former questions and see if there are answers for you to accept and therefor close those questions. – JvdV
Thank you I updated the question – user3163545
The formula you try in your edited question will never work for different sheets. You must use curly brackets as in =COUNT(FILTER(UNIQUE(flatten({A2:F2;Sheet1!A22:F22})), ARRAYFORMULA( COUNTIF(flatten({A2:F2;Sheet1!A22:F22}),UNIQUE(flatten({A2:F2;Sheet1!A22:F22})))>1 ))) – marikamitsos
Have you even tried my suggestion? – JvdV

2 Answers

3
votes

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

3
votes

Using a different approach,

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

or even

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

EDIT: Since one of the rows is formatted as text you'll have to convert to number

=ArrayFormula(countif(query(flatten({value(B4:U4);Sheet1!B2:U2}), "Select count(Col1) group by Col1"), ">1"))

enter image description here