Question description:
This user has given permission to use the problem statement for this
blog.
How do I prevent Duplicate entries in multiple columns in Excel using data validation in MSExcel 2016. I would like to prevent the user from data entering a code more than once in three column ranges
Solved by B. D. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
17/12/2017 - 04:14
Hi
User
17/12/2017 - 04:14
Hi
User
17/12/2017 - 04:15
I used this formula for a single column range and not I need a formula for 3 column ranges
=AND( ISNUMBER(MATCH(I5,AZ3:AZ69,0)), (COUNTIF($I$5:$I$83,I5)=1))
Excelchat Expert
17/12/2017 - 04:15
From what I understand, you would want to restrict the user from entering a value more than once in a particular column range.
Excelchat Expert
17/12/2017 - 04:15
Am I right?
User
17/12/2017 - 04:16
In 3 particular column ranges
Excelchat Expert
17/12/2017 - 04:16
Okay, give me a moment while I fix this for you.
Excelchat Expert
17/12/2017 - 04:20
Hi
Excelchat Expert
17/12/2017 - 04:21
Do you see the values in B2:D8
Excelchat Expert
17/12/2017 - 04:21
Do you see that where-ever there are duplicate values, theres a warning displayed.
User
17/12/2017 - 04:22
I see the B2:D8 values
Excelchat Expert
17/12/2017 - 04:23
Is this the output you are looking for?
User
17/12/2017 - 04:23
I also see the warning display. However, is it possible to prevent the user from entering the duplicates?
Excelchat Expert
17/12/2017 - 04:24
Yes,
Excelchat Expert
17/12/2017 - 04:24
Let me do that for you.
User
17/12/2017 - 04:25
Thank you VERY much :>)
User
17/12/2017 - 04:31
Are you still there?
Excelchat Expert
17/12/2017 - 04:31
Hi,Yes.
Excelchat Expert
17/12/2017 - 04:31
I'm working around it.
Excelchat Expert
17/12/2017 - 04:32
In Excel we generally have a error alert tab in data validation.
Excelchat Expert
17/12/2017 - 04:32
In Google Sheets that functionality seems to be missing.
Excelchat Expert
17/12/2017 - 04:32
Give me a moment while Ifigure that out for you.
User
17/12/2017 - 04:32
Please take your time. I got a message asking me if I needed more time
Excelchat Expert
17/12/2017 - 04:33
Bear with me for a couple more minutes
User
17/12/2017 - 04:34
Without a doubt! THANK YOU!!!
Excelchat Expert
17/12/2017 - 04:40
Hey!
Excelchat Expert
17/12/2017 - 04:40
It's fixed
Excelchat Expert
17/12/2017 - 04:40
Please try entering repetitive values in B2:D8
Excelchat Expert
17/12/2017 - 04:40
When you enter a value that repeats
Excelchat Expert
17/12/2017 - 04:40
It throws an error
User
17/12/2017 - 04:41
Ok so what formula do I enter in Data Validation.
Excelchat Expert
17/12/2017 - 04:42
=COUNTIF($B$2:$D$8,B4)=1
Excelchat Expert
17/12/2017 - 04:42
This is the formula I entered in
Excelchat Expert
17/12/2017 - 04:42
the data validation Sheet
User
17/12/2017 - 04:43
Sorry left out one condition. The columns are not adjacent to one another. How does that change the syntax?
Excelchat Expert
17/12/2017 - 04:44
Just change the range in count if accordingly
User
17/12/2017 - 04:44
Please give an example
User
17/12/2017 - 04:46
???
Excelchat Expert
17/12/2017 - 04:46
Sure
Excelchat Expert
17/12/2017 - 04:46
Give me a moment
User
17/12/2017 - 04:46
=COUNTIF($B$2:$B$8,B4)=1,COUNTIF($D$2:$D$8,B4)=1, COUNTIF($F$2:$F$8,B4)=1
Excelchat Expert
17/12/2017 - 04:46
Yes
Excelchat Expert
17/12/2017 - 04:46
Yes
Excelchat Expert
17/12/2017 - 04:47
But instead of , use a +
User
17/12/2017 - 04:47
=COUNTIF($B$2:$B$8,B4)=1, +COUNTIF($D$2:$D$8,B4)=1, COUNTIF($F$2:$F$8,B4)=1
User
17/12/2017 - 04:48
Sorry I mean =COUNTIF($B$2:$B$8,B4)=1,+COUNTIF($D$2:$D$8,B4)=1,+ COUNTIF($F$2:$F$8,B4)=1
Excelchat Expert
17/12/2017 - 04:48
=COUNTIF($B$2:$B$8,B4)=1+COUNTIF($D$2:$D$8,B4)=1+COUNTIF($F$2:$F$8,B4)=1
User
17/12/2017 - 04:48
thank you!!!!
Excelchat Expert
17/12/2017 - 04:48
:)
User
17/12/2017 - 04:49
I need to copy the data validation to other cells. How do I do that without having to correct the syntax
Excelchat Expert
17/12/2017 - 04:50
Do you want to discuss anything else?
Excelchat Expert
17/12/2017 - 04:50
In the Data validation tab
Excelchat Expert
17/12/2017 - 04:50
Please change the range you want the condition to be applied to
User
17/12/2017 - 04:51
Will need to go into each cell to do that?
Excelchat Expert
17/12/2017 - 04:51
No not necessary
Excelchat Expert
17/12/2017 - 04:51
When you open the data validation tab
Excelchat Expert
17/12/2017 - 04:52
Do you see the Cell Range option?
Excelchat Expert
17/12/2017 - 04:52
Please enter the range of cells in that text box
User
17/12/2017 - 04:53
I do not have a cell range option in data validation
Excelchat Expert
17/12/2017 - 04:58
Hey
Excelchat Expert
17/12/2017 - 04:58
Please see this
[Uploaded an Excel file]
User
17/12/2017 - 04:58
YesI have columns of 68 rows. It woul
Excelchat Expert
17/12/2017 - 04:58
There was some network connectivity issue, that is why the delay in my reply.
Excelchat Expert
17/12/2017 - 04:58
Sorry.
User
17/12/2017 - 04:59
This is an example of one cell formula
User
17/12/2017 - 04:59
=AND(ISNUMBER(MATCH(E13,AZ3:AZ68,0)),(COUNTIF($E$5:$E$83,E13)=1(COUNTIF($T$5:$T$83,E13))+ COUNTIF($U$5:$U$83, E13) =1))
User
17/12/2017 - 05:00
I would love to avoid correcting the MATCH
Excelchat Expert
17/12/2017 - 05:01
Uh, I'm sorry. I didn't get you completely.
User
17/12/2017 - 05:02
Well the data validation formula also includes going to a list of codes for the user to enter only and then restricts the entering only once in the three columns
Excelchat Expert
17/12/2017 - 05:03
Yes
Excelchat Expert
17/12/2017 - 05:04
Isn't that the similar output you are looking for?
User
17/12/2017 - 05:04
Each column has 68 rows. If I copy the MATCH formula I would need to correct the list range in each cell.
User
17/12/2017 - 05:04
=AND(ISNUMBER(MATCH(E13,AZ3:AZ68,0)),(COUNTIF($E$5:$E$83,E13)=1+COUNTIF($T$5:$T$83,E13))+COUNTIF($U$5:$U$83, E13) =1))
Excelchat Expert
17/12/2017 - 05:06
You want to do this using a formula?
Excelchat Expert
17/12/2017 - 05:06
But Data Validation is easier right
Excelchat Expert
17/12/2017 - 05:06
?
User
17/12/2017 - 05:06
yes PLEASE
Excelchat Expert
17/12/2017 - 05:06
The implementation through data validation is very simple.
User
17/12/2017 - 05:07
Yes I use the formula in the Custom data validation
Excelchat Expert
17/12/2017 - 05:07
Do you want to consider that?
User
17/12/2017 - 05:07
Yes
Excelchat Expert
17/12/2017 - 05:07
You're just facing the problem in expanding the condition to a range of cells right?
Excelchat Expert
17/12/2017 - 05:08
Were you able to spot Cell Range in the Data Validation Sheet?
User
17/12/2017 - 05:08
Is there a way to put the data validation formula in cells without needing to go back and correcting the formula?
User
17/12/2017 - 05:08
No
Excelchat Expert
17/12/2017 - 05:09
I sent you a screenshot
Excelchat Expert
17/12/2017 - 05:09
With Cell Range in the Data Validation dialog box
User
17/12/2017 - 05:10
I thought I need to use the Custom as validation criteria?
Excelchat Expert
17/12/2017 - 05:11
Yes the validation filter would be custom
Excelchat Expert
17/12/2017 - 05:11
But the Cell Range is a different option
User
17/12/2017 - 05:12
did you email it to varnadoca1@gmail.com
Excelchat Expert
17/12/2017 - 05:12
[Uploaded an Excel file]
Excelchat Expert
17/12/2017 - 05:12
I've reshared it here again
Excelchat Expert
17/12/2017 - 05:12
Please check and let me know
User
17/12/2017 - 05:12
give me a minute
Excelchat Expert
17/12/2017 - 05:13
Sure
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
