Home

Formula to validate duplicates

%3CLINGO-SUB%20id%3D%22lingo-sub-836286%22%20slang%3D%22en-US%22%3EFormula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836286%22%20slang%3D%22en-US%22%3E%3CP%3EIm%20trying%20to%20made%20a%20formula%20thats%20preventing%20duplicate%20entries%20in%20my%20worksheet.%20I%20want%20to%20check%20table%20A%20from%20position%208%20to%203000%20and%20give%20the%20user%20a%20varning%20message%20if%20a%20duplicate%20occurs.%3C%2FP%3E%3CP%3EHave%20tried%20videos%20from%20youtube%20but%20they%20did%20not%20work.%20A%20message%20allways%20pops%20up%20and%20till%20me%20to%20use%20an%20apostrophe%20in%20the%20beginning%20of%20the%20formula.%20PLEASE%20HELP%3C%2FP%3E%3CP%3EBengt%20from%20Sweden%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-836286%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836331%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403164%22%20target%3D%22_blank%22%3E%40Gubbedelux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Euse%20below%20formula%20for%20the%20data%20validation%20for%20desired%20range.%20Change%20all%20the%20%22B%22s%20to%20fit%20your%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(%24B%241%3A%24B4%3B%22%3D%22%26amp%3BB4)%3D1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836600%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836600%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20you%20response.%20Another%20message%20poptup%20that%20says.%20ONE%20OF%20THE%20AREAS%20YOU%20MENTIONED%20COULD%20NOT%20BE%20FOUND.%20Do%20you%20have%20any%20other%20idea%3F.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-838008%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-838008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403164%22%20target%3D%22_blank%22%3E%40Gubbedelux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839218%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839218%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20it%20works%20like%20a%20charm.%20Thank%20you%20kindly%3C%2FP%3E%3CP%3EBest%20regards%20Bengt%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-839590%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20validate%20duplicates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-839590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403164%22%20target%3D%22_blank%22%3E%40Gubbedelux%3C%2FA%3E%26nbsp%3B%26nbsp%3Blease%20mark%20this%20as%20best%20solution.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gubbedelux
New Contributor

Im trying to made a formula thats preventing duplicate entries in my worksheet. I want to check table A from position 8 to 3000 and give the user a varning message if a duplicate occurs.

Have tried videos from youtube but they did not work. A message allways pops up and till me to use an apostrophe in the beginning of the formula. PLEASE HELP

Bengt from Sweden

5 Replies

@Gubbedelux 

use below formula for the data validation for desired range. Change all the "B"s to fit your need.

 

=COUNTIF($B$1:$B4;"="&B4)=1

Thanks for you response. Another message poptup that says. ONE OF THE AREAS YOU MENTIONED COULD NOT BE FOUND. Do you have any other idea?.@erol sinan zorlu 

@Gubbedelux 

Please check the attached file.

Thanks it works like a charm. Thank you kindly

Best regards Bengt

@erol sinan zorlu 

@Gubbedelux  lease mark this as best solution.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
217 Replies