I have an excel document that has a row that can contain duplicates but not one cell on top the other
Fine Not Fine
10 10
20 20
30 20
40 30
I have an excel document that has a row that can contain duplicates but not one cell on top the other
Fine Not Fine
10 10
20 20
30 20
40 30
In the second cell use a custom data validation rule:
=A2<>A1
This assumes your data starts in A1.
Then copy that cell, and Paste Special/Validation to all the cells below it that you want to restrict.
6StringJazzer thanks for your reply. Either I am not understanding fully or it is not working as I would like
Here is an example of what i am wanting it to look like
Can you clarify your question? Your title says you want to prevent it. Do you want to prevent it, or just turn it red if it happens?
I have added two sheets.
The first one uses a conditional formatting rule to turn a cell red if it duplicates the cell in the previous row.
=AND(A1<>"",A1=OFFSET(A1,-1,0))
The second one uses data validation so it is not possible to enter a duplicate:
=A2<>A1
Try to enter a 20 in either column.
6StringJazzer you are awesome. Sorry about the confusion. I did want to prevent it from happening. When you sent your first response I noticed it turned the cells red. So I went with that scenario.
I really like this one User not allowed to enter dupe. Thanks for everything
Yes you are correct I did try it as conditional formatting and not data validation
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks