Generieke formule
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Samenvatting
Om het eerste duplicaat in een lijst te markeren, kunt u een formule gebruiken die is gebaseerd op de AANTAL.ALS-functie. Optioneel kunt u volgende duplicaten markeren met een andere markering. In het getoonde voorbeeld is de formule in cel C4:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Deze formule is in de kolom gekopieerd, van C4 naar C11.
Uitleg
In de kern is deze formule samengesteld uit twee sets van de AANTAL.ALS-functie verpakt in de ALS-functie. De buitenste IF + AANTAL.ALS controleert eerst of de betreffende waarde (B4) meer dan eens in de lijst voorkomt:
=IF(COUNTIF($B$4:$B$11,B4)>1
Als dit niet het geval is, retourneert de buitenste ALS-functie een lege tekenreeks ("") als eindresultaat. Als de waarde meer dan eens voorkomt, voeren we nog een IF + AANTAL.ALS-combo uit. Deze doet het werk van het markeren van duplicaten:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
Dit deel van de formule gebruikt een uitvouwbare verwijzing ($ B $ 4: B4) die groter wordt naarmate de formule naar beneden in de kolom wordt gekopieerd. (De eerste B4 in het bereik is absoluut (vergrendeld), de tweede is relatief en verandert dus als de formule naar beneden in de lijst wordt gekopieerd).
Onthoud dat dit deel van de formule alleen wordt uitgevoerd als de eerste AANTAL.ALS een getal groter dan 1 heeft geretourneerd. De formule controleert dus bij elke rij het aantal binnen het bereik tot aan de huidige rij. Als de telling 1 is, markeren we het duplicaat met "x", aangezien dit de eerste is die we hebben gezien. Als het niet 1 is, weten we dat het een volgend duplicaat moet zijn, en markeren we met "xx"
Basisformule
Om het eerste duplicaat in een lijst alleen met een 0 of 1 te markeren, kunt u deze uitgeklede formule gebruiken, die een uitbreidend bereik en de AANTAL.ALS-functie gebruikt.
=(COUNTIFS($B$5:B5,B5)=2)+0
Deze formule retourneert alleen 1 als een waarde tweemaal is aangetroffen - de eerste keer dat dit voorkomt, retourneert nul:
Om de tweede en alle volgende exemplaren te markeren, is de formule in F5 hierboven:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Opmerking: in beide voorbeelden is het optellen van nul slechts een eenvoudige manier om WAAR en ONWAAR waarden op 1 en 0 te zetten.
Bovendien maakt het gebruik van COUNTIFS in plaats van COUNTIF het mogelijk om waarden in andere kolommen te evalueren als onderdeel van de test op duplicaten. Elke extra kolom moet ook worden ingevoerd als een uitbreidend bereik.
