Excel dubbele waarden verhinderen

In gevallen waarin het noodzakelijk is dat een gebruiker in Excel unieke waarden invoert en er dus geen dubbele waarden mogen voorkomen, kan dat afgedwongen worden met behulp van gegevensvalidatie. Stel kolom A bevat de volgende gegevens:

dubbele_waarden_verhinderen1_215x300

In dit geval is vooraf ingesteld dat in kolom A geen dubbele waarden mogen worden ingevoerd. Dit is als volgt gedaan. Zorg dat de invoerkolom leeg is. Selecteer vervolgens vanuit A2 de cellen A2:A100 en kies voor de optie Gegevensvalidatie (deze optie is op het lint bij de tab Gegevens te vinden, maar dat kan per Excel-versie verschillen). Kies vervolgens in het scherm van de gegevensvalidatie bij het tabblad Instellingen bij Toestaan voor de optie Aangepast:

dubbele_waarden_verhinderen2_400x329

Bij de formule dient dan dan de volgende formule te worden ingegeven:

=AANTAL.ALS(A$2:A$100;A2)=1

(NB: Vervang bij Engelstalige Excel-versies AANTAL.ALS door COUNTIF

In dit geval wordt de invoer gecontroleerd op dubbele waarden voor A2 tot en met A100. Dit kan uiteraard aangepast worden. Eventueel kan op het tabblad Foutmelding een specifieke fouttekst ingegeven worden die verschijnt als de gebruiker een dubbele waarde invoert. Kies daarna voor OK en daarmee is de controle op het invoer van dubbele waarden ingesteld voor het celbereik A2:A100.

Als nu bijvoorbeeld in bovenstaand voorbeeld in cel A11 de waarde 5 ingevoerd wordt, dan is dat niet mogelijk en verschijnt onderstaande foutmelding:

dubbele_waarden_verhinderen3_485x115

Deze melding verschijnt ook als wordt geprobeerd om een al ingevoerde waarde te wijzigen in een waarde die al voorkomt.

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

10 reacties op Excel dubbele waarden verhinderen

  • Fantastische uitleg. Heel erg bedankt. Je bent geweldig!

    • Graag gedaan en bedankt voor je compliment.

  • Je zou dit ook kunnen bereiken met de somals-functie. Als binnnen het bereik de betreffende waarde al voorkomt zal de somals-functie de waarde 2 toekennen, wat zou moeten resulteren in de foutmelding.

    • Bedankt voor je reactie. Er zijn inderdaad meerdere mogelijkheden om dit te realiseren, Ik denk niet dat het met de functie SOM.ALS lukt, maar met de functie AANTAL.ALS lukt het zeker. Dit is eigenlijk nog handiger en ik heb de tekst hierop aangepast.

  • Dank voor de uitleg. Is dit ook mogelijk voor controle in meerdere (tab)bladen in hetzelfde bestand of moet de formule dan aangepast worden?

    • Ja, dat kan ook over meerdere tabbladen. De formule moet dan wel aangepast worden. Dan wordt het iets als:

      =AANTAL.ALS(A$2:A$100;A2)+AANTAL.ALS(Blad2!A$2:A$100;A2)=1
      • Dank, weer een stukje puzzel. Werkt dit dan ook dat als ik in blad 2 of blad 3 iets invul hij controleert op dit niet al in blad 1 of respectievelijk 2 of 3 vermeld staat.

        • Ja, er wordt gecontroleerd op het uniek zijn over alle bereiken die in de formule voor gegevensvalidatie worden opgenomen.

  • Hallo,

    Ik gebruik deze oplossing ook en dat werk goed.
    Alleen als je CTRL+D gebruikt, dan werkt het niet.

    Hiervoor zou je in VBA onder “ThisWorbook” de volgende code kunnen gebruiken:

    Private Sub Workbook_Open()
    On Error Resume Next
    
      'schakelt CTRL+D uit
       Application.OnKey "^d", ""
    
    End Sub

    Om te zorgen dat CTRL+D wel weer in andere bladen werkt de volgende code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    
         'schakelt CTR+D weer in
         Application.OnKey "^d"
    
    End Sub
    • Bedankt voor de aanvulling. Het klopt dat gegevensvalidatie beperkingen heeft. Ook met bijvoorbeeld ‘kopiĆ«ren’ – ‘plakken als waarden’ kun je niet toegestane gegevens in een cel met gegevensvalidatie plaatsen zonder dat er een foutmelding komt.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.