Gisteravond had Joe uit Boston op de Radioshow Computer America van Craig Crossman een vraag:
Ik heb een kolom met factuurnummers. Hoe kan ik Excel gebruiken om de duplicaten te markeren?
Ik stelde voor om voorwaardelijke opmaak en de AANTAL.ALS-formule te gebruiken. Hier zijn de details om dat te laten werken.
We willen voorwaardelijke opmaak instellen voor het hele bereik, maar het is gemakkelijker om een voorwaardelijke opmaak in te stellen voor de eerste cel in het bereik en vervolgens die voorwaardelijke opmaak te kopiëren. In ons geval heeft cel A1 de kop van het factuurnummer, dus ik selecteer cel A2 en selecteer in het menu Opmaak> Voorwaardelijke opmaak. Het dialoogvenster Voorwaardelijke opmaak begint met de eerste vervolgkeuzelijst met de tekst "Celwaarde is". Als u de pijl ernaast aanraakt, kunt u "Formule is" kiezen.
Nadat u "Formule is" hebt geselecteerd, verandert het uiterlijk van het dialoogvenster. In plaats van vakken voor "Tussen x en y", is er nu één formulevak. Deze formulebox is ongelooflijk krachtig. U kunt elke formule typen die u kunt bedenken, zolang die formule maar WAAR of ONWAAR wordt.
In ons geval moeten we een AANTAL.ALS-formule gebruiken. De formule die u in het vak moet typen is
=COUNTIF(A:A,A2)>1
In het Engels zegt dit: 'kijk door het hele bereik van kolom A. Tel hoeveel cellen in dat bereik dezelfde waarde hebben als wat in A2 staat. (Het is erg belangrijk dat de' A2 'in de formule verwijst naar de huidige cel - de cel waarin u de voorwaardelijke opmaak instelt. Dus - als uw gegevens in kolom E staan en u stelt de eerste voorwaardelijke opmaak in E5 in, zou de formule dat zijn =COUNTIF(E:E,E5)>0). Vervolgens vergelijken we om te zien of dat aantal is> 1. Idealiter, zonder duplicaten, zal het aantal altijd 1 zijn - omdat cel A2 zich in het bereik bevindt - zouden we precies één cel in kolom A moeten vinden die dezelfde waarde bevat als A2.
Klik op de Formaat… knop
Nu is het tijd om een onaangenaam formaat te kiezen. Er zijn drie tabbladen bovenaan dit dialoogvenster Cellen opmaken. Het tabblad Lettertype is meestal het eerste, dus je zou een vet, rood lettertype kunnen selecteren, maar ik hou van iets irritanters. Ik klik meestal op het tabblad Patronen en kies voor helder rood of fel geel. Kies de kleur en klik op OK om het dialoogvenster Cellen opmaken te sluiten.
U ziet het geselecteerde formaat in het vak "Voorbeeld van te gebruiken formaat". Klik op OK om het dialoogvenster Voorwaardelijke opmaak te sluiten …
… en er gebeurt niets. Wauw. Als dit de eerste keer is dat u voorwaardelijke opmaak instelt, zou het erg leuk zijn om hier wat feedback te krijgen dat het werkte. Maar tenzij je het geluk hebt dat de 1098 in cel A2 een duplicaat is van een andere cel, is de voorwaarde niet waar en lijkt het erop dat er niets is gebeurd.
U moet de voorwaardelijke opmaak van A2 naar de andere cellen in uw bereik kopiëren. Met de cursor in A2, doe Bewerken> Kopiëren. Druk op Ctrl + spatiebalk om de hele kolom te selecteren. Gebruik Bewerken> Plakken speciaal. Klik in het dialoogvenster Plakken speciaal op Formaten. Klik OK.
Hiermee wordt de voorwaardelijke opmaak naar alle cellen in de kolom gekopieerd. Nu - eindelijk - zie je enkele cellen met de rode opmaak, wat aangeeft dat je een duplicaat hebt.
Het is informatief om naar cel A3 te gaan en het voorwaardelijke formaat na de kopie te bekijken. Selecteer A3, druk op od om voorwaardelijke opmaak te openen. De formule in het vak Formule is is gewijzigd om te tellen hoe vaak A3 in kolom A voorkomt: A.
Opmerkingen
In Joe's vraag had hij maar 1700 facturen in het assortiment. Ik heb 65536 cellen met voorwaardelijke opmaak opgezet en elke cel vergelijkt de huidige cel met 65536 andere cellen. In Excel 2005 - met meer rijen - wordt het probleem nog erger. Technisch gezien had de formule in de eerste stap kunnen zijn:=COUNTIF($A$2:$A$1751,A2)>1
Als u de voorwaardelijke opmaak naar de hele kolom kopieert, had u in plaats daarvan alleen de rijen met gegevens kunnen selecteren voordat u de speciale opmaak plakken.
Meer
Het andere probleem dat ik na de vraag heb beschreven, is dat je een kolom echt niet kunt sorteren op basis van een voorwaardelijk formaat. Als u deze gegevens moet sorteren zodat de duplicaten zich in één gebied bevinden, volgt u deze stappen. Voeg eerst een kop toe aan B1 met de naam "Dupliceren?". Typ deze formule in B2: =COUNTIF(A:A,A2)>1.
Klik met de celaanwijzer in B2 op de hendel voor automatisch aanvullen (het kleine vierkantje in de rechter benedenhoek van de cel) om de formule helemaal naar beneden te kopiëren.
U kunt nu sorteren op kolom B aflopend en A oplopend om de probleemfacturen bovenaan het bereik te krijgen.
Bij deze oplossing wordt ervan uitgegaan dat u BEIDE dubbele facturen wilt markeren, zodat u handmatig kunt bepalen welke u wilt verwijderen of corrigeren. Als u niet wilt dat het eerste optreden van het duplicaat te markeren, kunt u de formule aan te passen te zijn: =COUNTIF($A$2:$A2,A2)>1. Het is belangrijk om de dollartekens precies in te voeren zoals weergegeven. Hiermee worden alleen alle cellen van de huidige cel bekeken, op zoek naar dubbele vermeldingen.
Met dank aan Joe uit Boston voor de vraag!
