Stop duplicate data entry with these two EASY methods in Excel

[@dhananjaypinjan2643]

You are awesome Chandoo and since long time 🙏🙏🙏

[@chandoo_]

Thank you Dhananjay...

[@MrSameer744]

you are my first guru of excel......... Thank you sir.....

[@chandoo_]

Thank you Sameer.

[@wayneedmondson1065]

Hi Chandoo. Great tutorial! I did the shrinking Data Validation List formula this way: =SORT(FILTER(players,COUNTIFS(D6:D11,players)=0)). So, eliminated the need for NOT() by making the TRUE condition that the COUNTIFS result = 0. Just another way to get there. Thanks for sharing your tips and tricks :)) Thumbs up!!

[@chandoo_]

Nice work! I should have used =0 check as that is easier to explain. Sometimes you miss the obvious ones. :)

[@wayneedmondson1065]

@@chandoo_ Thanks :)) Cheers!!

[@kenmcmillan2637]

Great tips sir! By the way, your office looks awesome! I love the way it looks.....very clean look!

[@chandoo_]

Thanks so much! I have been slowly decorating the home office in last 6 months and I like the way it looks now. It brings a smile on my face every time I walk in.

[@lifetolive5149]

Thank You Sir Chandoo for the nice tutorial and worthwhile video.

[@jrr_ddp]

Actually You are awesome. Thanks For all your Videos Related to Excel, Power BI and Much more about Data Analyst

[@swapnilwankhede3440]

Super Chandoo. Today I am going to implement the first one formula in my invoice reprocessing tracker so that I won't allow duplicates data. Thanks its great help.

[@chandoo_]

Great 👍

[@swapnilwankhede3440]

@@chandoo_ hi Chandoo, I observed that, the technique got failed when you use copy and paste in column C.

[@sayedaglover4913]

YOU are awesome Chandon...thanks for your help which I use everyday.....🇲🇺🇬🇧

[@chandoo_]

Thank you Sayeda...

[@shahswapnilh1421983]

Wow , you are the one from whom I learnt dependent drop-down and now "self shrinking selection" !! Thanks a lot 🙂

[@chandoo_]

My pleasure 😊

[@msantosh1220]

Awesome trick on how to create self shrinking drop down. Thanks for the last line that made my day. I was also able to download the free copy of excel formula for myself. G8 work

[@mercyrn35]

Your channel is amazing! You have really helped me tremendously! I am a new subscriber! You really have made this very simple for me😊

[@prakashbabu6173]

ఎక్సెల్ expert ma చందు గారు 🙏 You are really awesome sir👍

[@chandoo_]

చాలా థన్యవాదాలు అండి 😀

[@noeltrindade]

Very creative way of putting this. thoughtful and super helpful

[@chandoo_]

Thanks so much!

[@vikramraghuwanshi9455]

Super explanation about a data validation . Explanation isa awsome . , mind blowing.

[@chandoo_]

Glad you liked it

[@canirmalchoudhary8173]

Chandoo you are bang awesome. It was really tricky to digest but finally opened your excel file and succeeded.

[@chandoo_]

Glad to hear that... Yeah, the second one is tricky, but super valuable in business situations.

[@canirmalchoudhary8173]

@@chandoo_ in second one you applied COUNTIFS very uniquely to create array of matching counts. I thinks due to dynamic array functionality, if we switch like this in XMATCH with ISNUMBER, COUNTIFS, XLOOKUP, VLOOKUP it will create array, that can directly be used in filter

[@chandoo_]

yeah.. you can. Let me know what you come up with :)

[@canirmalchoudhary8173]

@@chandoo_ sure

[@rogeriooliveira4934]

Hello, I would like to thank you for your contributions and work on KZbin. Would you please have any other material in excel that you don't have on KZbin to share sales versus forecast accuracy? Thank you so much

[@Nexus_Dawn]

You can sign up for Chandoo’s email list here:chandoo.org I think there are some courses on Udemy too.

[@chandoo_]

Hi Rogerio... Thanks for the love and appreciation :) I have a website with over 1000 articles, tips and examples. Please visit chandoo.org/wp/ and learn :)

[@Excelambda]

Great Video!! for 2nd DV also this works: =SORT(FILTER(players,ISNA(XMATCH(players,D6:D11)))) (countifs is faster, but sometimes , if more array precalculations are involved to refine a list, countifs range argument could be a problem)😉✌

[@chandoo_]

Thank you.. Great tip on using XMATCH...

[@prakashsathyapriya]

You are simply rocking Mr.Chandoo. but i wonder, you are yet to do a video about Excel data entry forms and hope you will do soon.. Expecting asap. Thank you

[@chandoo_]

Thanks Prakash... I am planning to add a video on data entry forms and some advanced uses.

[@prakashsathyapriya]

@@chandoo_Thank you very much 😀

[@JosefMillerDK]

YOU ARE DEFINITELY AWESOME CHANDOO!

[@chandoo_]

😊😍

[@pankajknowledgeworld]

मेरे program run करने के लिए auto shrink hi ढूंढ रहा था.. Got it formula 😊

[@jiajiaphotography]

I have just implented the second method in my worksheet. Awesome and smart 👍

[@chandoo_]

Great 👍

[@mahansor8295]

Thanks Chandoo 👍👍

[@pritikane4448]

I couldn’t say that right even for one time😆.. I am sure you also attempted more than 6 times.. would love to see the bloopers raw footage for this one..

[@chandoo_]

I went back and watched the footage 🤔. Strangely, I got that on one take 😎. I even said that 3 more times (after I aske the viewers to say it 6 times), but chose to remove it in the final cut. I am surprised I didn't stutter as I am not a native speaker of English and I often mess up.

[@pritikane4448]

you are awesome..

[@hydarzachary]

Hi Chandoo, Is there any way to make two different data validation for a single range of data? Thank you

[@Amank3107]

Awesome 👍 thanks for the wonderful video

[@chandoo_]

Glad you enjoyed it

[@rajonhs]

🤯🤯🤯 - you're a legend!!

[@chandoo_]

😎😍

[@dilipshedge8503]

Your all videos are excellent 👍

[@chandoo_]

Thank you so much 😀

[@cogwheelLearning]

Really creative video! Awesome work!

[@odokonyerorobinson2475]

Hi chandoo, i appreciate the great work you are doing I am having challenges getting the exact number of participants who have benefited from a given program, the participants can recieve more than one. however when i try to get the number of participants it goes beyond the overall number, how can you help me? Am looking forward to your response

[@hydarzachary]

Hi Chandoo, do you know if you copy a value and paste from validated range show duplicate? The formula stop duplicate only when you type same value twice.

[@bairikrishna0808]

Thanks bhayya... This is a cool content... I am new to your channel, did you already have a video on conditional drop-down list?

[@chandoo_]

You are welcome. Please see these pages Cascading drop downs - kzbin.info/www/bejne/h4HGdGONi8-miqs chandoo.org/wp/robust-dynamic-cascading-dropdowns-without-vba/

[@guptabhishek]

Awesome work Chandoo! Been learning a lot from you! I had a query I was struggling with, maybe you could give some advice? Is there any way to give a NOT filter in this advanced filter? Like I want to filter out values that have "*Laptop*" and "*Desktop*". I ask this because I am trying to create a complex boolean string search. For eg, ("*Dell*" OR "*Asus* NOT ("*Laptop*" OR "*Desktop*")). How do I implement such complex searches in Excel? Would love to get some help. Thanks!

[@chandoo_]

Thanks Abhishek.. I will have to research this. Let me comeback to you.

[@bskrmusic3932]

Awesome Anna !

[@sachink5233]

Great! Thank you.

[@chandoo_]

You are welcome!

[@CeliaAlvesSolveExcel]

No! Not me. You are Awesome, Chandoo! :)

[@chandoo_]

😊😀

[@h.n.yousaf]

Just wondering. How come you don't have a million subscribers already?

[@johndurran614]

Excellent

[@chandoo_]

😍

[@shoaibrehman9988]

Actually you are Awesome

[@chandoo_]

😊😍

[@pandharinathjoshi6565]

Great think

[@jonelatendido9836]

What excell version you are using?

[@myrrihina]

does this work when Microsoft forms are automatically inputted into excel online? or is this only for manual entries

[@chandoo_]

With forms, you can't set up data validation rules like this. But forms have their own "validation rules" and "branching criteria". So try that.

[@myrrihina]

@@chandoo_ if you could assist I would really appreciate it. All of the sudden Microsoft forms entries have started duplicating in excel online.

[@Saad.PS2009]

Thank you so much Sir

[@chandoo_]

Most welcome

[@bijumonmolilkunjukunju4968]

so useful

[@BlockFHatter]

Chandoo, May I ask a favour of you. I use the xlsb file format a lot as it make files much smaller and they are still capable of running macros. Are there any downsides to using that file format as I would imagine many millions of gigabytes could be saved if more people used it.

[@chandoo_]

Hi Barry... There is nothing wrong with XLSB files as such. I use them too when I need to add a macro or save on file size.

[@sajiphilipwithnature]

Thank you

[@chandoo_]

You're welcome

[@aneesh47]

Hi, i am also wanted to be small youtuber like this.. Can u tell how the video gets zoomed to focus while u do some action in cells??

[@chandoo_]

Hi Aneesh... I am using Camtasia to do my screen recordings. You can download it from techsmith website.

[@aneesh47]

@@chandoo_ thanks for your reply.

[@kumarsreenivas]

Awesome

[@gvinodnair]

Just Wow!

[@chandoo_]

Thank you Vinod...

[@ExcelHubPro]

Sir create vedios on Web excel plz

[@chandoo_]

Thanks Rashid.. I will include some examples on web excel in future videos.

[@elit6366]

Hello! I’ve been subscribed but I didn’t get the book 😔

[@chandoo_]

Hi Eli... Visit chandoo.org/wp/subscribe/ and add your email there. If you already did that, just check your email inbox / junk mail folder for an email from me with the details. Happy reading.

[@HusseinKorish]

This's Perfect

[@chandoo_]

Thank you...

[@-excelsip7027]

Thank You What if I pick all the List Item I get A #CALC in the first cell on the list, How I can mange this

[@chandoo_]

If you use a formula or incorrect reference in the data validation, you get #CALC! error. Check the cell values and fix that.

[@-excelsip7027]

@@chandoo_ The formula and the reference is correct, But only when I Pick all the items in the list I get that message (#CALC), Try it please and you will understand what I mean

[@chaharumesh]

Hi chandoo, Instead of typing a duplicate number ,if we drag the above number then this data validation will not work

[@chandoo_]

This is a limitation of Excel. Any data validation rules are not checked when dragging or copying values.

[@chaharumesh]

Thank you chandoo . I am big fan of your teaching method

[@johnyouseph4038]

thanks

[@chandoo_]

You're welcome!

[@rebellionofindia43]

hello chandoo i want to make validation for emp id like Imp id 001 imp id 002 Imp id 003 and want that duplicate validation on it if i use contif(------)=1 that only work for numbers, not text with number (Imp id 001) it any video link which i can see

[@rahulmoradiya6873]

If we paste black cell in data validation cell then data validation remove, how to fix data validation cell....?

[@chandoo_]

Once you have added the validation, you can protect the spreadsheet. Make sure the input cells are "unlocked" from format cells > protection option. This can prevent such unwanted copy paste. Another option is to train your users. Excel Data validation is not bullet proof.

[@rahulmoradiya6873]

@@chandoo_ thanks for Replay, I have only second option, because my staff paste from different sheet also.

[@johnpaulosalto5685]

3:05 😂 do you have any formula for tounge twisters.

[@chandoo_]

Just use REPT("Self shrinking selection set ", 6) and Speakcells...

[@PHWiest]

Speaking of Awesome: Here's a great book: You Are Awesome: How to Navigate Change, Wrestle with Failure, and Live an Intentional Life by Neil Pasricha

[@chandoo_]

Sounds like a good book. I just read up about the author and looked the reviews on Amazon. I will see if our local library stocks it, else I might order it.

[@rash9366]

you too awesome chandoo (ɔ◔‿◔)ɔ♥

[@mrmusicloverable]

Hahahah... "Dead simple".

[@chandoo_]

😆😆😆