Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

COUNTIF in data validation to check for duplicate

0

I'm Trying to use COUNTIF in data validation (custom) to check that a number entered hasn't already been used further up the same column.  I have =COUNTIF(F:F,F5)=1 which will trigger an error alert if a duplicate number is used and it works fine.

I want to keep it simple which is why I'm trying to do it without a macro, but how can I copy this down all cells in the column without having to retype it for every one?

Answer
Discuss

Answers

1
Selected Answer

Hi JonP

If cell F5 is validated as Custom using the formula:

=COUNTIF(F:F,F5)=1

just select that cell, copy it (e.g. Ctrl+C) then select column F (or a range within it) and go ribbon Home/ Paste (down arrow) / Paste Special... and under the Paste section, click Validation then OK.

The whole column (or your range) will be validated and won't allow duplicate values.

Hope this fixes your problem. If so, please remember ot mark thisd Answer as Selected.

Discuss

Discussion

Thank you, that works perfectly.

I don't think I've ever knew about the validation option under paste special, let along knowing what it did!  Maybe it's something that Don should add a tutorial for (or maybe he already has...?!)
JonP (rep: 39) Nov 11, '24 at 8:24 am
Glad that worked for you and thanks for selecting my Answer, JonP. Excel has so many features that's it's hard for anyone to know everything- there's always something new to learn, even after using it for years.

You may know there's a Validation section in the Tutorials but I just found that what I suggested above is the 4th "trick" in Don's tutorial here: 5 Must Know Paste Special Tricks
John_Ru (rep: 6612) Nov 11, '24 at 8:38 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login