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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
See if this one works (applied with B2 as the active cell).

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

hyd1956

New Member
Joined
Jun 26, 2020
Messages
40
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Unfortunately that hasn't worked. When trying to use that formula it's not allowing me any data entry into the cells at all
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
Not sure what I was thinking there, that formula was never going to work :oops: try this one instead

=(COUNTIF($B$2:$B$6000,B2)+COUNTIF($D$2:$D$6000,B2)+COUNTIF($F$2:$F$6000,B2))=1
 

hyd1956

New Member
Joined
Jun 26, 2020
Messages
40
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

That is absolutely perfect, thank you :)
 

kmcmunn

New Member
Joined
Aug 6, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi! I have a similar situation, but when I recreate that formula using the cell ranges for my data, it does not work. I want to prevent a duplicate entry that is dependent on column C (name), D (email), and L (activity) in which the same person/email cannot be entered for the same activity.

Any thoughts are greatly appreciated!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

=(COUNTIFS($C:$C,$C2,$D:$D,$D2,$L:$L,$L2)=1
 

kmcmunn

New Member
Joined
Aug 6, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you for the reply. The formula above does not work.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,015
Office Version
  1. 2019
Platform
  1. Windows
Does not work in what way?

Allows duplicates to be entered?
Does not allow anything to be entered?
Something else?
 

kmcmunn

New Member
Joined
Aug 6, 2020
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Sorry. It allows for duplicate entries to be typed into all of the cells used in the formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,280
Messages
5,546,948
Members
410,764
Latest member
Dedeke
Top