Seeking a formula to determine duplicate numbers in a spreadsheet row?

Question

Is there a formula to let me count if there are duplicates showing up in a row?
If I have 3 to 6 columns of individual numbers, I'd like to know if any numbers are duplicated in any particular row? To keep this simple, the best way to think of it is lottery numbers: If I have rows with 7-7-5; then 4-5-2 below that; then 1-5-7; then 2-2-5; then 1-0-9; then 1-1-3; then 1-0-1; then 5-4-2. You can see that the first, fourth, sixth and seventh row had duplicates.

Is there a formula that would calculate that for me?

Answer 1

You can use SUMPRODUCT for this, given 3 columns:

=IF(SUMPRODUCT((A1:C1<>"")/COUNTIF(A1:C1,A1:C1&""))<>3, "DUPES", "")

The SUMPRODUCT function here functions like an array formula without being one, and iterates through the entire range, and the result is the number of unique values. You then just compare that to the fixed number of columns you have, and assign TRUE/FALSE results as needed.

Answer 2

I'm not sure about a single formula. Since it's only 3-6 columns, just repeat this formula in the 3-6 cells next to your original data. (this example is for 3 columns)

=countif(a1,a1:a3) 

Then if you really need it in one column... In the column beside that formula I just gave put an OR statement testing for > 1 for each column inside an IF statement.

=if(or(d1>1,e1>1, f1>1),"DUPLICATES!!","no dupes")