**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.*