How to Prevent User to Enter Duplicate using Data Validation

[PRIMA]

Dear All,

I want to prevent user from entering duplicate text or numbers in a cell using the Data Validation.

Thank you & Happy New Year!!!

 

[iliace]

For example, this custom rule will only allow users to enter unique values (occurring 1 time or less) in range A1:A20

=COUNTIF($A$1:$A$20, A1)<2

Note, you have to select A1:A20, with cell A1 active, when you create this validation rule.

 

[PRIMA]

Thank you so much iliace, that really works great and wornderful.

I have one more question, if you don't mind. It is possible to enter more than 1 data validation in the same cell range. Thanks a lot again.

 

[pleeseemailme]

PRIMA,
A quick search turned this up: multiple validation in a single cell A far as I know, only 1 validation is allowed per range, but you can fool the validation into validating multiple things with formulas.
I think it will depend on what you want to do for your multiple data validation...depend on whether a formula can be written for it or not. What specifically do you want to validate?

 

[iliace]

The Range object of Excel (which is any cell or combination of cells) contains only a single Validation object, and that Validation object contains a single validation type (such as List, Date, Whole Number, Custom, etc).

Therefore, it is only possible to assign a single validation rule to any cell or range.

However, if you really need multiple validations, you could develop some VBA code to do so. You would have to monitor worksheet changes, check whether any changed cells are within your validation range, and then execute code to ensure the data being entered/modified is valid.

Of course, pleeseemailme is also correct - you can create a more sophisticated formula to check for multiple conditions.

 

[PRIMA]

pleeseemailme, thank you so much for your quick reply. I've got the first custom formula above, and the other is I want to limit the cell from entering only the "Whole Number" from 1 to 999. Is that possible Sir? Thanks again

 

[pleeseemailme]

PRIMA, I'm a big fan of helper columns. The way I would solve this would be to create a helper column with the following formula:

=IF(ISTEXT(A1),COUNTIF($A$1:$A$20, A1)<2,AND(A1>=1,A1<=999,COUNTIF($A$1:$A$20, A1)<2))+0

Where range A1:A20 is the data you are trying to validate and A1 is the first cell. Fill this all the way down. Then, select the data you want to validate (A1:A20). Select data validation -> Custom and for the formula input: =F1=1 . Where F1 is the first cell of the helper column. Make sure that the active cell is the top cell in the list. In other words, when you select the cell range to validate, make sure you select from the top down and not the bottom up.

Let me know if this works.

For what it's worth, you can place the above formula directly into the data validation custom formula and it will work, but you can test it out first with the helper column.

 

[iliace]

Don't forget, it also needs to be a whole number.

I think this formula will account for everything. However, it will not allow text entries - not sure if that's what you want.

=AND(COUNTIF($A$1:$A$20, A9)<2, IFERROR(INT(A9)=ROUND(A9, 4), FALSE), A9>=1, A9<=999)

 

[pleeseemailme]

That is true. To allow only integers between 1 and 999 as well as text, amend the formula to:

=IF(ISTEXT(A1),COUNTIF($A$1:$A$20, A1)<2,AND(A1>=1,A1<=999,COUNTIF($A$1:$A$20, A1)<2,IFERROR(INT(A1)=A1,FALSE)))+0

Thanks for the reminder and formula for the Integer part Iliace

 

[PRIMA]

Perfect! Thank you so much iliace for your time. This is actually what I need. Thank you again for sharing.