@@computertutoring After apply these data validation rules to two/more colums, that coloums drop-down lists are not working. How can I apply double data validation(custom & list) in same coloum at same time? TIA
@@gaziakteruzzaman8011 Make sure you copy the validation, using paste special, to the cells you need to validate - the formula for 3 columns would be - =COUNTIFS($A$2:$A$1048576,$A2,$B$2:$B$1048576,$B2,$C$2:$C$1048576,$C2)
This formulation is not working if I have some formulas in column A & B & not a manual entry. Meaning there will be zero or blank combinations as well. Pls suggest me on this.
This formula will only work for people who are entering data. If you want to remove duplicates that are already there, you can use remove duplicates on the Data tab
Thank You, so much. It was very helpful for me as I was looking for something like this. One more query, it is not working when I am copying and pasting value only working when type the value. Is there any way to solve above problem??
Hi sorry I didn't see your other question. I'm afraid that you are correct data-validation only works when you are typing in values or when you're editing a single cell. Though I have found a macro that may be worth checking out. answers.microsoft.com/en-us/msoffice/forum/all/excel-data-validation-copy-and-paster-can-over/cdd762b3-93c8-4c42-845d-e015ed0e096c
Hi can this be done fron multiple sheets? Say I have 1 main sheet where all enteries go. Once I go through work they get moved into different sheets. I need to stop or highlight any new work coming in on sheet 1 duplicating thats already been moved. Please help if you can.
Computer Tutoring just noticed this is Excel and I am using google sheets. I have made 2 different sheets now rather than multiple so I am hoping I have made it alot easier.
Hi I would use the data validation with the COUNTIFS instead of using the columns you would use the rows A2:G2 or whatever column letter. Maybe you've already found a solution. Sorry it took a couple of days to get back to you.
what happens if some one copy paste the same record twice - i don't think the formula will catch it. It works only when some one manually type the entry. Also, how does it works when your validation columns are not in sequence order let say you have to validate A, C and D ( skip B) and only when duplicate found in A , C and D , error should be given
Data Validation is really best used for when you enter data not copying. Although be interested to know what results from the copy and paste error. Also I places dollar signs in to fix the Columns check out absolute references for more
thanks
a step by step guide , continue ur videos it will help many people
Great job!
Thank you and Well done. However this video is more about tracking and identifying duplicates than blocking or avoiding.
Good Point!
Thank you. It is a great learning.
This was worth watching, if just for the F4 shortcut of adding the dollar signs. Thank you.
Thanks
Great sir, I was just looking for this,
Happy you found it worked 💪
Thanks a lot for your great tutorial....
Thanks mate 👍🏽
@@computertutoring After apply these data validation rules to two/more colums, that coloums drop-down lists are not working. How can I apply double data validation(custom & list) in same coloum at same time?
TIA
@@gaziakteruzzaman8011 Make sure you copy the validation, using paste special, to the cells you need to validate - the formula for 3 columns would be - =COUNTIFS($A$2:$A$1048576,$A2,$B$2:$B$1048576,$B2,$C$2:$C$1048576,$C2)
This formulation is not working if I have some formulas in column A & B & not a manual entry. Meaning there will be zero or blank combinations as well. Pls suggest me on this.
This formula will only work for people who are entering data. If you want to remove duplicates that are already there, you can use remove duplicates on the Data tab
Thank You, so much.
It was very helpful for me as I was looking for something like this.
One more query, it is not working when I am copying and pasting value only working when type the value.
Is there any way to solve above problem??
So glad it worked
Hi sorry I didn't see your other question. I'm afraid that you are correct data-validation only works when you are typing in values or when you're editing a single cell. Though I have found a macro that may be worth checking out. answers.microsoft.com/en-us/msoffice/forum/all/excel-data-validation-copy-and-paster-can-over/cdd762b3-93c8-4c42-845d-e015ed0e096c
Hi can this be done fron multiple sheets? Say I have 1 main sheet where all enteries go. Once I go through work they get moved into different sheets. I need to stop or highlight any new work coming in on sheet 1 duplicating thats already been moved. Please help if you can.
Should be fine so long as the validation ranges are correct.
Computer Tutoring so to add the range would I just use same formula and add the letter & number value with sheet 2, sheet 3 etc....
@@danmurphy7338 Pretty much. Let me know how it goes. To make this easier you might use named ranges
Computer Tutoring just noticed this is Excel and I am using google sheets. I have made 2 different sheets now rather than multiple so I am hoping I have made it alot easier.
You save my day 🤗
That's great! :-)
Thanks alot
No worries 👍🏽
@computer tutoring
I have a question how i can stop same value of first column into next column
Hi I would use the data validation with the COUNTIFS instead of using the columns you would use the rows A2:G2 or whatever column letter. Maybe you've already found a solution. Sorry it took a couple of days to get back to you.
what happens if some one copy paste the same record twice - i don't think the formula will catch it. It works only when some one manually type the entry. Also, how does it works when your validation columns are not in sequence order let say you have to validate A, C and D ( skip B) and only when duplicate found in A , C and D , error should be given
Data Validation is really best used for when you enter data not copying. Although be interested to know what results from the copy and paste error. Also I places dollar signs in to fix the Columns check out absolute references for more
For me it didn't work writing (,) in the formula but it worked using a (;) instead of (,).
for example =COUNTIFS($A$2:$A$91;A2)
That's right if you're on the continent you'll need to use semi colon. Thanks for the comment, am sure it will help a lot.
thanks alot
can you put it in vba code please ?
See if I get time ;)
how to add multiple columns
2022- useless ☹️