Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer
    
This is the correct way to do it. –  Raystafarian Feb 24 '12 at 14:44

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")
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.