Data validation formula to prevent duplicates in any cell across multiple columns

[hyd1956]

Hi

I'm trying to prevent duplicates from being entered into any cell in multiple columns and across any row, the formulas I've been trying haven't worked. This is what I'm using, is there anyway to do this? thanks for thelp

=COUNTIFS($B$2:$B$6000,$B2,$D$2:$D$6000,$D2,$F$2:$F$6000,$F2)<=1

 

[jasonb75]

The way that I read your question was that you wanted it to reject duplicate rows (same name and email acceptable with different activity or same activity with different name or email). From a quick test it does what you asked for.

 

[kmcmunn]

Perhaps I am not applying it properly in the Data Validation function then. I am highlighting only the columns that I need validated and inserting the formula. When I test it, the next line in the spreadsheet allows me to enter the exact same information from the line above and does not give an error message.

 

[jasonb75]

As I said earlier

applied as validation rule to C2, then copied to the other rows / columns to be validated as needed.

If you're trying to apply it to entire columns then you would need to base the formula against row 1, not row 2.
When choosing the type of validation rule, you must use 'Custom' for this to work.