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

hyd1956

New Member
Joined
Jun 26, 2020
Messages
40
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,001
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

kmcmunn

New Member
Joined
Aug 6, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,001
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,072
Messages
5,545,822
Members
410,707
Latest member
SanTrapGamer
Top