Data validation with 3 Criteria's already have data form

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
I have been working on this for a bit now...watched videos on all of these type of criteria but i cant seem to find one with all three.
I have a database with contacts, basic name, phone number date called and notes. What I'm trying to do is avoid duplicates when the data entry is
being inputted by multiple users of course. I understand the COUNTIF formula, and the fact that there are millions of rows so I cant put unlimited rows so I added an end range.
What I'm having trouble with is the whole number formula, with the data validation custom I add the AND then I get stuck with the LEN formula...because I input no more than
10 characters for a phone number. However, I don't like the row to just be numbers I want it to have dashes. I originally thought that if I eliminate all the dashes first then add the formula...then I could add the column format numbers but then what happened was is showed up as error because there were more than 10 characters.

Sorry if i am all over the place so let me make it clear what I'm trying to do
1. COUNTIF($C$2:$C$63,C2)=1
2. LEN(C2)<10
together do i input the equals sign first then the AND? then add both formulas like so....
=ANDCOUNTIF($C$2:$C$63,C2)=1,LEN(C2)<10

So I've tried this and i get errors on all rows i removed the LEN and get no errors but i need the limit of characters to avoid adding more numbers to the cell.
My very last question is i tried checking the COUNTIF formula and it works by itself but only until the 63 row...it lets me add duplicate past that row that's why i need it to be a future number like 999999 but it gives me an error when i do that.

Help me please...i already have a crm for other types of things but this needs to be very basic...entry data available for my agent to log in and input new numbers and call those that are on their and add notes.
I don't understand macros or vba but I've looked into them and it just seems like it gives me more than I need. I'm just looking for something simple. Yet i know I'm making it complicated with this one column i need 3 validation for.

So to be clear the 3 criteria is to continue this with additional rows being inputted no limit.
THANK YOU SO MUCH!
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
In this part try the following formula:

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C1:C2000,C2)=1)
sweet let me try that
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
I'm not understanding something, with the "Phone Number" format, you write the 10 numbers without dashes, for example: 1234567890. You press enter and automatically the data appears like this: (123) 456-8899

Now, you need to tell us which solution you want to continue with.
With a formula, let's say in cell D2 to check cell C2.
Or with data validation?
You must also tell us how you want to capture the phone: 1234567890 or 12-34-56-78-90 or 123-456-7890 or 12345-67890
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
If you want to capture a phone with 10 digits and 2 dashes, let's say 123-456-7788
use the following formula in data validation

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(SUBSTITUTE(C2,"-","")+0),LEN(C2)=12,COUNTIF(C:C,C2)=1,MID(C2,4,1)="-",MID(C2,8,1)="-")
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
If you want to capture a phone with 10 digits and 2 dashes, let's say 123-456-7788
use the following formula in data validation

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(SUBSTITUTE(C2,"-","")+0),LEN(C2)=12,COUNTIF(C:C,C2)=1,MID(C2,4,1)="-",MID(C2,8,1)="-")
than you sooo much!!! it finally worked BUTTTTTT (lol) a couple of things happened.
So first let me tell you what i wanted with the phone numbers. I only want the dashes just like you did it 123-456-7788 but i just think its too much work.
Let me explain....so it worked for the column C the previous phone numbers on there were cleaned with no dashes so it created an error for all of them. So what i did is removed data validation formula you gave me and then formatted those cells with the phone number with dashes custom...then i did the formula and it still gave me the errors. ThatS why i think the dashes are not sooo important.
My priority is formatting the C column with phone numbers but do not allow duplicates (in the data entry form ive created) and no more than the 10 numbers to the phone number to prevent errors.
I've got 3 other sheets (with hundreds of contacts) i need to format like this so i really want something simple and easy. Eventually when i change these particular contacts into a crm then ill get pickier about dashes lol
Please just help me with the no duplicated phone numbers in that column at all (with my starting cell in that column C is 2) AND to add that formula for each new entry. (PRIORITY) the dashes are not required. thank you for all your help.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

So you are going to capture the number like this: 123456790 and in cell format you have something like this ###-###-####

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C:C,C2)=1)
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
So you are going to capture the number like this: 123456790 and in cell format you have something like this ###-###-####

Data Validation
  • Data >> Data Tools >> Data Validation
  • Setting >> Custom
  • Formula:
Excel Formula:
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C:C,C2)=1)
Hello DanteAmor! thank you for all your help...just to be clear before input the data validation formual...do i click on the top of the column to select the whole column or just anywhere within the table will that matter??
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

You can select all column C, or all cells where you want it to apply, for example: C2:C100
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
You can select all column C, or all cells where you want it to apply, for example: C2:C100
i could also use C:C range too right thats what that means....?
so i did and to test it out i inputted my number with a letter and it didnt stop me then i tried again to test the duplicated and it still didnt stop me...thoughts?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,753
Office Version
  1. 2007
Platform
  1. Windows
If you are going to choose the whole column, you need to change the formula in that sense.

Excel Formula:
=AND(ISNUMBER(C1),LEN(C1)=10,COUNTIF(C:C,C1)=1)
 

vsaenz

New Member
Joined
Jul 27, 2021
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
If you are going to choose the whole column, you need to change the formula in that sense.

Excel Formula:
=AND(ISNUMBER(C1),LEN(C1)=10,COUNTIF(C:C,C1)=1)
=AND(ISNUMBER(C2),LEN(C2)=10,COUNTIF(C2:C300,C2)=1)
okay so this is how i would change it by inputting the starting cell C2 all the way to lets say C300? this will avoid duplicates
the LEN formula will avoid no more than 10 characters in each cell and the ISNUMBER will avoid any letters only numbers.
Is that correct? I am trying to understand the formula...what about the FALSE word do i add that at the end so that if it input data doesn't follow this
critieria an error will occur correct??....oh and what about the dollar signs do i need those added anywhere?
 

Forum statistics

Threads
1,143,690
Messages
5,720,311
Members
422,275
Latest member
Maria95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top