Das Auffinden von doppelten Einträgen in langen Tabellen gehört zu den häufigen Aufgaben des geplagten Anwenders: Auftragsnummern, Kontonummern, Kundennummern und so weiter, die Liste ließe sich beliebig erweitern.
Zum Glück stellt Excel einige praktische Hilfsmittel zur Verfügung, um solche Dubletten in einzelnen Spalten schnell zu identifizieren. Die Betonung liegt hier auf „einzelnen“ Spalten. Was ist jedoch, wenn mich nur solche doppelten Einträge interessieren, die über mehrere Spalten hinweg identisch sind. Also zum Beispiel Adressen?
Auch dafür gibt es eine Lösung.
Dem Problem mit doppelt vorkommenden Tabelleneinträgen habe ich mich auch schon in einem anderen Artikel gewidmet, den du gerne nochmal hier nachlesen kannst. Dort werden auch ein paar andere Techniken beschrieben.
Der heutige Beitrag befasst sich speziell mit den Möglichkeiten der bedingten Formatierung.
Dubletten in einzelnen Spalten
Doppelte Auftragsnummern etc. lassen sich am schnellsten über eine bedingte Formatierung erkennen. Sollte dir dieses extrem einfache Hilfsmittel bisher noch nicht bekannt gewesen sein, hier nochmal die Vorgehensweise am Beispiel von IP-Adressen. Nehmen wir an, ich bin Systemadministrator und habe eine Liste von IP-Adressen:
Liste mit IP-Adressen
Nun möchte ich ohne großen Aufwand die doppelten Werte farblich hervorheben. Genau dafür gibt es eine vordefinierte Regel in den bedingten Formatierungen: Erst die Liste markieren (1), danach das Menü „Start | Bedingte Formatierung | Regeln zum Hervorheben von Zellen | Doppelte Werte…“ öffnen (2).
Vordefinierte Formatierungsregel für doppelte Werte
Die vorgeschlagenen Werte im nächsten Fenster kann ich unverändert übernehmen, da hier bereits die Option „Doppelte Werte“ voreingestellt ist:
Duplikate werden farbig markiert
Nachdem ich das Fenster mit OK geschlossen habe, werden mir sofort alle Duplikate farbig angezeigt. Voraussetzung für diese Art der bedingten Formatierung ist, dass immer nur die jeweilige einzelne Zelle für den Abgleich herangezogen werden soll.
Dubletten mit mehreren Spalten
In einem anderen Szenario funktioniert diese vordefinierte Regel nicht, nämlich dann, wenn mehrere Zellen berücksichtigt werden müssen. Also beispielsweise Adressen:
Beispiel: Adressenlste
Eine Dublette ist hier nur dann gegeben, wenn die Werte über alle Spalten hinweg identisch sind. Bevor wir hier eine entsprechende bedingte Formatierung einrichten können, sollten wir uns überlegen, wie dieses Problem generell zu lösen ist.
Im Klartext formuliert lautet die Regel in etwa so:
Du bist eine Dublette, wenn die jeweilige Kombination aus Vorname UND Nachname UND Straße UND Postleitzahl UND Ort mehr als einmal in der Liste vorkommt. Wir zählen also und arbeiten dabei mit mehreren Bedingungen.
Zu diesem Zweck gibt es in Excel die Funktion ZÄHLENWENNS:
=ZÄHLENWENNS(Bereich 1; Kriterium 1; Bereich 2; Kriterium 2; Bereich 3; Kriterium 3; ....)
Die Bereiche sind dabei die verschiedenen Spalten, das Kriterium ist der Eintrag in der jeweiligen aktuellen Zeile. Bezogen auf meine Beispielliste sieht das also so aus:
=ZÄHLENWENNS($A$2:$A$30;$A2;$B$2:$B$30;$B2;$C$2:$C$30;$C2;$D$2:$D$30;$D2;$E$2:$E$30;$E2)
Sieht wie ein ziemliches Monster aus, ist aber eigentlich leicht zu durchschauen:
Die ZÄHLENWENNS-Funktion
Ganz wichtig:
Da wir die Formel anschließend einfach nach unten kopieren wollen, müssen die Bereiche zwingend mit absoluten Bezügen angegeben werden. Also $A$2:$A$30, $B$2:$B$30 und so weiter.
Die Kriterien hingegen dürfen nur mit gemischten Bezügen angegeben werden. Die Spalte ist mit dem Dollarzeichen fixiert, die Zeilennummer jedoch nicht. Also $A2, $B2 und so weiter. Ansonsten stimmen die Bezüge nicht mehr, wenn die Formel kopiert wird.
Als Ergebnis seht die 1, wenn diese Adresse in der ganzen Liste nur ein einziges Mal vorkommt. Ansonsten zeigt die Zahl eben an, ob sie zweimal, dreimal oder noch öfter auftaucht:
Wie oft kommt die Adresse vor?
(Die farbigen Markierungen habe ich noch manuell gesetzt)
Für unsere Dublettenprüfung interessiert uns jetzt weniger die exakte Zahl 1, 2 usw., es reicht eigentlich aus zu wissen, ob das Ergebnis größer als 1 ist. Daher hängen wir an die Formel noch eine kleine Prüfung an und erhalten als Ergebnis nur noch WAHR oder FALSCH:
Erweiterung der Formel
Und mit dieser Formel haben wir alles, was wir für eine eigene bedingte Formatierungsregel benötigen.
Wir markieren also zunächst wieder die Adressenliste ohne die Überschriften (1), in meinem Beispiel den Bereich A2:E30 und legen dann eine neue Formatierungsregel an (2):
Eigene Formatierungsregel anlegen
Im nächsten Fenster markieren wir oben den Eintrag „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ (3) und kopieren dann die fertige ZÄHLENWENN-Funktion in das Formelfeld (4):
=ZÄHLENWENNS($A$2:$A$30;$A2;$B$2:$B$30;$B2;$C$2:$C$30;$C2;$D$2:$D$30;$D2;$E$2:$E$30;$E2)>1
Die ZÄHLENWENNS-Funktion kommt zum Einsatz
Wichtig ist nur, dass die Zeilennummern in der Formel auch mit der ersten markierten Zeile übereinstimmen. Und wie man im Hintergrund schon sieht, werden jetzt wirklich nur die vollständigen Duplikate hervorgehoben.
Wie man an diesem Beispiel sieht, macht es durchaus Sinn, sich bei eigenen Formatierungsregeln die benötigten Formeln erst einmal in der Tabelle zusammenzubasteln. Dann sieht man auch schnell, ob sie grundsätzlich funktioniert oder nicht.
Falls du beim Nachbauen trotzdem Probleme hattest, kannst du dir meine Beispieldatei hier herunterladen.
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
ich habe ein Problem, bei der diese Funktion die Grundlage bilden, jedoch vermutlich noch ergänzt werden müsste (Excel 2010):
Die Spalten A und B enthalten Werte, die auf Dubletten hin untersucht werden sollen. Die Spalte C enthält jeweils einen bestimmten Betrag (€). Dieser Wert soll am Ende der Spalte (Summe C) dann nur einmal bei evtl. vorkommenden Dubletten (Spalten A und B) in der zu bildenden Summe berücksichtigt werden. Wie müsste die Summenformel lauten?
Ergänzung: Gemeint sind Dubletten in Spalte A und B im o. g. Sinne zusammengenommen! Nur die mehrfachen Beträge nur einmal zu zählen würde nicht zum Ziel führen, da diese auch bei anderen Einträgen in den Spalten A+B identisch sein könnten, dort jedoch wiederum bei der Summenbildung berücksichtigt werden müssten.
Hallo Uwe,
ich bin mir nicht sicher, ob ich es richtig verstanden habe:
Wenn also ein Dublette im Sinne von identischen Einträgen in den Spalte A + B existiert, dann soll der dazugehörige Wert in Spalte C aber nur einmal in der Summe berücksichtigt werden?
Aber welcher der Werte in C ist dann relevant, wenn diese Dubletten unterschiedliche Werte in Spalte C enthalten?
Oder habe ich dich doch falsch verstanden?
Schöne Grüße,
Martin
Ja, richtig verstanden, die Beträge in Spalte P (nicht C, war nur beispielhaft) sind in diesem Fall dann auch doppelt und dürfen nur einmal in die Summe P einfließen. Käme es nur auf doppelt bzw. mehrfach vorhandene Beträge in Spalte P an, wären diese sicherlich auch auf andere Weise (einfacher) herauszufiltern, es könnte theoretisch! aber vorkommen, dass sich bei mehrfach vorhandenen Einträgen in Spalte P die Werte in Spalte A + B zusammengenommen unterscheiden und diese daher bei der Summe P wiederum nur einmal berücksichtigt werden dürften. Ich habe inzwischen eine Lösung mit einer Hilfsspalte AL (die Tabelle ist in Wirklichkeit größer) gefunden, in der die Werte aus den Spalten A + B verkettet werden, die Matrixformel lautet: {=SUMME(WENN(ZÄHLENWENN(INDIREKT(„AL3:AL“&ZEILE(3:29));AL3:AL29)=1;P3:P29))}. Ich hatte lange versucht, die VERKETTEN-Funktion in diese Summenformel zu integrieren, also ohne Hilfsspalte auszukommen, was aber leider nicht funktioniert hat. Falls du noch eine einfachere Variante weißt (auch VBA), wäre ich dir dankbar. Da diese Tabelle je nach Fall eine unterschiedliche Anzahl von Zeilen haben kann, würde mich interessieren, ob es eine Möglichkeit gibt, statt der „29“ einen Wert für die „letzte Zeile“, die bei der Summenbildung zu berücksichtigen ist, einzugeben, so dass man die Formel nicht ständig anpassen muss. Herzlichen Dank!
Danke, super erklärt – wieder was dazugelernt!
Gern geschehen!
Schöne Grüße,
Martin
Vielen Dank für Ihre Tipps rund um Excel. Für viele Benutzer, sowie auch meine Teilnehmer in meinen Microsoft Office Schulungen, sind diese Websites eine große Hilfe zur Selbsthilfe und ich empfehle dies in meinen Seminaren für den Arbeitsalltag.
Viele Grüße aus Bochum
Christopher Methler
Hallo Herr Methler,
das freut mich sehr zur hören. Vielen Dank für das tolle Feedback und die Empfehlungen!
Schöne Grüße,
Martin
Hallo Herr Weiß,
eine sehr schöne und einfache Methode um Duplikate über mehrer Zeilen zu identifizieren.
Nun möchte ich aber daraus eine Liste erzeugen, in der keine Duplikate mehr vorkommen.
Wie kann ich das bewerkstelligen?
Mit freundlichen Grüßen
Christian
Hallo Christian,
dafür würde ich entweder die integrierte Bereinigungsfunktion (Menü „Daten | Duplikate entfernen“) oder Power Query verwenden. Auch dort gibt es bereits eine eingebaute Funktion dafür.
Schöne Grüße,
Martin
Danke für die tolle Anleitung dazu, wie man in Excel doppelte Einträge löschen und Duplikate entfernen kann. Gerade, wenn man unkonzentriert Werte eingibt oder reinkopiert, kann sich da schnell mal etwas doppeln. Ich werde mir das ganze gleich nochmal in Ruhe ansehen, damit ich mir auch merke, wie man es macht.
Vielen Dank!
Sehr gern geschehen!
Schöne Grüße,
Martin
Hallo Herr Weiß,
super Erklärung!
Ich habe jedoch in diesem Themengebiet schon seit längerer Zeit ein „Problem“ zu dem mir einfach keine Lösung einfällt..
Ich möchte mir mit Hilfe einer bedingten Formatierung duplikate farbig markieren lassen.
Die Überprüfung muss jedoch über mehrere (Insgesamt 54) Tabellenblätter funktionieren.
Wenn ich also in Tabellenblatt 30, in irgendeine beliebige Zelle (A1) „XYZ“ eingebe, soll es sofort rot markiert werden, falls in Tabellenblatt 12 in irgendeiner anderen Zelle (B6) bereits „XYZ“ steht.
Innerhalb eines Tabellenblattes ist die Überprüfung/Suche nach Duplikaten kein Problem, i know.. kennen Sie jedoch eine Lösung, wie dies über mehrere Tabellen- (Register)blätter funktionieren könnte?
Im Voraus vielen Dank und LG
Hallo Flo,
vielen Dank für das Feedback!
Für das beschriebene Problem weiß ich leider auch keine Lösung. Unabhängig davon würde ich Ihnen ohnehin dringend davon abraten, eine bedingte Formatierung über 54 Tabellenblätter und eine unbestimmte Anzahl von Zellen überhaupt in Erwägung zu ziehen. Bedingte Formatierungen haben leider auch die Eigenschaft, die Performance in Excel massiv zu verschlechtern, wenn sie exzessiv eingesetzt werden. Und für das beschriebene Szenario sind sie einfach nicht geeignet.
Schöne Grüße,
Martin
Hallo,
vielen Dank für die super Anleitung!
Auch ich stehe jetzt vor dem Thema der Überprüfung und dann Anzeige der doppleten Werte über mehrere Tabellenblätter hinweg.
Ich möchte gerne immer die gleichen Spalten in den einzelnen Tabellenblättern auf Duplikate (Texte/Wörter) geprüft haben.
Dazu habe ich die bedingte Formatierung so erweitert:
=ODER(ZÄHLENWENN(‚Tabellenblatt 1‘!$A:$A;$A:$A)>1;ZÄHLENWENN(‚Tabellenblatt 2‘!$A:$A;$A:$A)>1;ZÄHLENWENN(‚Tabellenblatt 3‘!$A:$A;$A:$A)>1;ZÄHLENWENN(‚Tabellenblatt 4‘!$A:$A;$A:$A)>1;ZÄHLENWENN(‚Tabellenblatt 5‘!$A:$A;$A:$A)>1)
Im Grundprinzip funktioniert das Ganze, bzw. sollte, was es aber nicht macht.
Gibt es noch eine andere Möglichkeit übergreifend (über Tabellenblätter) die Duplikate zu markieren?
1.000 Dank
Hallo Tilo,
deine Formel hat zwei grundsätzliche Probleme:
Du verwendest in der ZÄHLENWENN-Funktion sowohl für den Suchbereich als auch für das Suchkriterium jeweils einen kompletten Zellenbereich. Das Suchkriterium darf sich aber nur auf eine einzelne Zelle beziehen. Also z.B.
ZÄHLENWENN(„Tabellenblatt 1“!$A:$A;$A)>1
Du verwendest mehrere ZÄHLEWENN-Funktionen mit einer ODER-Funktion in der bedingten Formatierung, die sich auf unterschiedliche Tabellenblätter beziehen. Jede dieser ZÄHLENWENN-Funktionen durchsucht aber nur den Bereich auf einem Arbeitsblatt. Falls im jeweiligen Arbeitsblatt der gesuchte Wert aber nur 1x vorkommt, wird er nicht gefunden, selbst wenn er auch auf den anderen Blättern jeweils einmal vorkommt.
Eine mögliche Variante wäre es, die Treffer auf den verschiedenen Blättern zu addieren und dann zu prüfen, ob es mehr als 1 Treffer gibt:
=ZÄHLENWENN(Tabelle1!$A:$A;A1)+ZÄHLENWENN(Tabelle2!$A:$A;A1)+ZÄHLENWENN(Tabelle3!$A:$A;A1)>1
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Ich habe mir eine Behelfslösung gebastelt, indem ich die Spalten, die ich im Hauptblatt nicht aktiv nutze oder sehe, mit „=Tabelle2!$AB1“ versehen habe, dann eben in meinem Fall mit zwei Spalten und die Dubletten-Abfrage quasi über diese Nachahmer-Spalten laufen lassen 🙂
Ich bin mir nicht sicher, wie praktikabel das bei 54 Tabellenblättern ist, würde die Prüfung aber auch nur bis maximal Zeile 5000 machen, sonst dauert es ewig mit der bedingten Formatierung.
Ist es auch möglich mit Excel eigenen Mitteln die ermittelten Duplikate zu löschen.
Meine Date hat bereits mehrere tausend Zeilen und ist somit extrem unübersichtlich.
Außerdem werden mit etlichen Makros Funktionen ausgeführt wo aufgrund der Größe der Datei die Rechnezeit schon ziemlich lange ist.
Gibt es da auch eine Lösung?
Danke schon mal im Voraus für die Antwort
lg
Cris
Hallo Chris,
zum Entfernen von echten Duplikaten gibt es tatsächlich eine Funktion:
Menü Daten | Duplikate entfernen
Was die Rechenzeit in deiner Datei angeht, wird es mit einem Tipp jedoch schwierig, dafür kann es unzählige Gründe geben. Neben der reinen Datenmenge liegt es meistens eher an den eingesetzten Formeln. Vielleicht hilft dieser Artikel ein wenig weiter:
https://www.tabellenexperte.de/excel-im-schneckentempo-volatile-funktionen/
Schöne Grüße,
Martin
Hallo,
ja klar, die Funktion ist mir bekannt, löscht aber nur Duplikate die sich auf eine Spalte beziehen.
Mein anliegen ist ja das ich die ermittelten Duplikate aus drei Spalten vergleiche und lösche.
Somit würde sich die Rechenzeit automatisch verkürzen da Exel ja nur mehr in einem drittel der bisherigen Daten nachsehen muss.
lg
Cris
Hallo Cris,
da solltest du dir diese Funktion nochmal genauer ansehen, denn sie kann sehr wohl mit mehreren Spalten umgehen. Man kann exakt auswählen, welche Spalten für die Duplikatsuche herangezogen werden sollen.
Schöne Grüße,
Martin
Hallo mir hilft die Formel leider nicht ganz weiter,
ich habe Spalte A-K.
Ich benötige Wahr nur wenn alle 3 Felder in Spalte B,C,G identisch sind, alle anderen Felder dürfen variieren.
VG
Nicole
Funktioniert doch, ich hatte nur einen Fehler gemacht.
VG
Nicole
Hallo Martin,
ich bin auf der Suche, Duplikate in Excel über mehrere Spalten, entfernen auf Deine Seite gestoßen. Ich habe da Problem noch nicht gelöst. Da bei mir folgendes der Fall ist:
Spalte 1 enthält: Ort, Land, Unternehmensname
Spalte 2 enthält: Ort, Land
Jetzt möchte ich in Grund eine Differnz aus 1 und 2 bilden. Ich möchte also die Spalte 2 von der Spalte 1 abziehen, sprich den Ort und das Land löschen. Es sollte in Spalte 1 nur noch der Unternehmensname und in Spalte zwei unverändert der Ort und das Land stehen.
Geht sowas?
Würde mich über ein Feedback freuen.
VG
Bernhard
Hallo Bernhard,
im Grunde geht es um das Aufteilen der Spalte 1 bzw. das Herauslösen eines Textteils. Dafür gibt es verschiedene Möglichkeiten:
– die Funktion über das Menü „Daten | Text in Spalten“
– Power Query
– Formellösungen, z.B. diese hier:
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Möglichkeit, deine Beispieldatei runterzuladen.
Ich war schon am Verzweifeln, nachdem ich mit deiner Vorgehensweise nicht die Dubletten aus verschiedenen Teilnehmerlisten finden konnte. Auch das Kopieren der Daten in eine neue xls-Datei brachte nur in Teilen das gewünschte Ergebnis. Warum auch immer?!
Letztendlich habe deine Formel kopiert, in meine xls-Datei eingefügt und den Datenbereich angepasst – voilà alle Dubletten wurden gefunden.
Ein gaaaanz großes DANKESCHÖN dafür!
Hallo Annette,
freut mich sehr, wenn du doch noch erfolgreich warst.
Schöne Grüße,
Martin
Servus, habe ich so gemacht, danke für die tolle Anleitung!!
Jetzt die wichtigste Frage: Wie lösche ich die Dubletten dann auf einmal?
LG Jonas
Ah habs schon geschafft. Trotzdem eine andere Frage: Ich hab jetzt die adressliste. Bei allen Kunden ist in Spalte F der Status hinterlegt. Historisch und Aktuell. Hier will ich alle mit historisch löschen. Hab schon die spalte F sortiert, allerdings waren dann die Werte den falschen Kunden zugeordnet. Wie gehe ich am besten vor?
Hallo Jonas,
du darfst nicht nur die Spalte mit dem Status sortieren. Du musst die ganze Tabelle sortieren, aber eben nach der Spalte mit dem Status:
Den Datenbereich markieren, dann Menü „Daten | Sortieren“ aufrufen. Und dort die Spalte mit dem Status angeben.
Schöne Grüße,
Martin
Hallo Martin,
danke für die gute Erklärung, hat auch geklappt.
Jetzt will ich aber die gefundenen Duplikate auch löschen. Welche Möglichkeit gibt es dazu?
viele Grüße
Bettina
Hallo Bettina,
wenn du mit der beschriebenen bedingten Formatierung arbeitest, kannst du die Tabelle nach der Zellenfarbe filtern. Und danach die betreffenden Zeilen löschen.
Schöne Grüße,
Martin
Toll erklärt nur mir hilft es leider nichts weil ich nicht zählen kann. Heisst bei mir sind nicht alle Spalten gleich befüllt sondern ich suchen in über 20.000 Zeilen zwischen Spalten C – J ob sich Duplikate zwischen C-J befinden.
Sind Materialien mit Beschreibungstexten. Manche Texte sind kürzer, manche länger und eben auf mehrere Spalten aufgeteilt.
Heisst Material mit kurzem Text kann von C-D einen Text haben und den gleichen nochmal von E-F
Material mit langem Text kann von C-F Text haben und den gleichen nochmal von G-J.
Noch habe ich keine Lösung gefunden die Dupletten hier zu finden. Leider. Ja das Problem bin ich vorm PC weil ich keine geeignete Formel dafür finde bzw. mir auch nicht einfällt.
Einspaltige od. wo in jeder Spalte dieselbe Art drinnen ist geht ja aber wenn es so wie hier variieren kann dann ist das doof.
lg
Hallo Sabrina,
ja, ein Materialstamm kann eine wahre Quelle der Freude sein…
Um bei deinem ersten Beispiel zu bleiben:
Material hat einen Text von C-D und in einer anderen Zeile den gleichen Text von E-F. Ich unterstelle mal, dass beim ersten Material dann die Spalten E-F und beim zweiten Material die Spalten C-D leer sind. Falls diese Annahme richtig ist, könntest Du in einer Hilfsspalte alle Textspalten zu einer einzigen zusammenfassen, z.B. so:
=GLÄTTEN(C1&D1&E1&F1)
Das wäre dann sozusagen der „Mastertext“. Und dort lassen sich natürlich ohne Probleme Duplikate finden.
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo Martin,
ich habe eine große Excel Tabelle die mit 14984 Zeilen sehr unübersichtlich ist. Dabei steht in Spalte E das Datum und in Spalte O eine Identifikationsnummer. Ich möchte mit ZÄHLENWENNS herausfinden welche Identifikationsnummern aus O öfter als einmal am gleichen Kalendertag vorkommen. Dazu habe ich diese Formel verwendet:
=ZÄHLENWENNS($E$2:$E$14984;$E2;$O$2:$O$14984;$O2)>1
Allerdings ist das Ergebnis bei der Zählung der WAHR und FALSCH Antworten unplausibel, es kommen viel zu viele WAHR heraus. Ist die Formel so wie ich sie verwende richtig? Zählt Excel jetzt wirklich ob die Nummer aus O nur einmal pro Kalendertag in E vorkommt??
Hallo Anselm,
ich denke, deine Formel passt schon. Für jede einzelne Zeile wird geprüft, ob das jeweilige Datum und die ID in dieser Zeile mehr als einmal vorkommen.
Wenn du dir unsicher bist, empfehle ich einfach eine Hilfsspalte in der du die beiden Zellen (Datum und ID) verkettest:
=E2&O2
Dann kannst du diese Hilfsspalte zählen lassen oder über bedingte Formatierung die Duplikate kennzeichnen.
Schöne Grüße,
Martin
Hallo Martin,
danke für diesen Artikel. Ich bin auf der Suche nach einer Möglichkeit, sämtliche einmalige Datensätze und sämtliche Dubletten automatisiert aufzulisten und sofern es eine Dublette, mir auch die Anzahl dieser Dubletten anzeigen zu lassen. Sofern es eine Dublette ist, ist hier noch wichtig, dass nicht nur die Anzahl angegeben wird, sondern zusätzlich auch noch aus der Spalte 1 die eindeutige ID aufgelistet wird, je nachdem wie viele Dubletten vorkommen, stehen dort auch so und so viele ID Nummern.
Hintergrund ist folgender: Es handelt sich um eine Türliste, jede Tür hat eine eindeutige ID (Spalte 1), jedoch hat jede Tür um die 50 verschiedenen Eigenschaften (Spalte 2-51), die größtenteils entweder zutreffen/nicht zutreffen (x oder -) oder in denen auch bis zu 10 unterschiedliche Angaben stehen können (z.B. Schlosstyp 1, 2, 3, 4, 4.1 usw). Bis dato arbeite ich per Hand mit Filtern und filtere mich durch, bis ich weiß ob es nur einmal so vorkommt oder ob es mehrere Türen mit den gleichen Eigenschaftenkombinationen gibt. Das ist wie Du Dir vorstellen kann extrem zeitaufwendig und fehleranfällig. Ich würde mich freuen, wenn Du eine Idee für einen Weg hast, dem ich erst einmal folgend kann.
Ich bedanke mich im Voraus.
VG
Tilman
Hallo Tilman,
man könnte das Problem vermutlich relativ einfach über eine Hilfsspalte lösen, in der die ganzen Eigenschaften zusammengefasst werden. Ich habe das mal mit einem kleinen Beispiel ausprobiert.
In Spalte M sind über die Funktion TEXTVERKETTEN die 5 Attribut-Spalten zusammengefasst. Die Formel in meinem Beispiel lautet:
=TEXTVERKETTEN("-";;H2:L2)
Daneben habe ich noch die Anzahl für jede Kombination berechnet:
=ZÄHLENWENN($M$2:$M$19;M2)
Wenn man dann noch eine bedingte Formatierung für doppelte Werte auf die Spalte M setzt, kann man ziemlich schnell die Duplikate erkennen und per Rechtsklick danach filtern (Filter | Nach dem Wert der ausgewählten Zelle filtern).
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo Martin,
Habe ein simples Problem, komme nun aber einfach nicht weiter und hoffe du kannst mit helfen…
Spalte A , B , C sind zu prüfen auf doppelte Werten wie folgt:
In Spalte A sind Werte die gesucht werden, Spalte B werden Werte eingescannt, Spalte C „=RECHTS(A1;47)“ angepasst damit Werte von Spalte A und Spalte C vom Format identisch sind und mit Bedingter Formatierung rot werden und gefunden werden. Das funktioniert auch
In der Spalte B wollte ich nun falls Werte doppelt eingescannt werden gelb werden, das man das gleich bemerkt, nur das funktioniert nicht…
Habe schon die Reihenfolge geändert, jedoch alles ohne erfolg,
Hallo Peter,
vielleicht könntest du hier noch die Formatierungsregeln und die angewendeten Bereiche reinschreiben, die du bisher verwendest.
Grundsätzlich könntest du mit einer Hilfsspalte (z.B. in Spalte D) arbeiten, welche die Spalten A und B zusammenführt:
D1: =A1&B1
Und dann nur für die Spalte B eine Formatierungsregel anlegt, die Duplikate in dieser Hilfsspalte prüft:
=ZÄHLENWENN($D$1:$D$100;$D1)>1
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo Martin,
da ich diesen Beitrag zum Thema „mehrspaltige Duplikate“ gerade genutzt habe, mal eine generelle Rückmeldung zu Deinen Artikeln.
Ich habe in den letzten Jahren immer mal wieder intensiv oder auch nur sporadisch mit Excel gearbeitet, musste dementsprechend häufig auch Dinge nachschauen, und habe in Deinen Artikeln IMMER gute, richtige bzw. für mich hilfreiche Informationen, Tipps und Tricks gefunden.
Du stellst mit den Inhalten, die Du hier veröffentlichst, aus meiner Sicht die Spitze dessen dar, was an Hilfen zu Excel im Netz verfügbar ist, daher wollte ich auch mal ein großes Lob hierzu loswerden! Super gut, verständlich, mit schrittweiser Entwicklung von Themen aufbereitet – echt TOP! Das hilft wirklich weiter und hat mir schon mehrfach Stress und viel Arbeitsaufwand erspart!
VG
Christian
Hallo Christian,
vielen Dank für dein tolles Feedback, ich bin echt geplättet!
Dann wünsche ich dir auch weiterhin viel Spaß hier auf dem Blog.
Schöne Grüße,
Martin
GutenTag Martin
Ich habe gemäss folgendem Link https://www.tabellenexperte.de/duplikate-in-excel-ueber-mehrere-spalten-finden/ zu einer Lösung von Ihnen „Dupletten in mehreren Spalten nachgebaut. Klappt alles super. Jedoch sollte ich das in einer intelligenten Tabelle haben, da klappt es aber nicht.
Ihre Lösung zu meiner intelligentenTabelle: =ZÄHLENWENNS($A$2:$A$20;$A2;$B$2:$B$20;$B2)>1
klappt!
Meine Variante zu meiner intelligenten Tabelle klappt nicht:
=ZÄHLENWENNS([Spalte1];[@Spalte1];[Spalte2];[@Spalte2])>1
Was muss ich ändern, dass die bedingte Formatierung klappt?
Hallo Thomas,
leider akzeptiert das Formelfeld im Dialogfenster für die bedingte Formatierung keine strukturierten Verweise, so wie sie in intelligenten Tabellen üblich sind. Das ist ein Ärgernis, das Sie leider nicht umgehen können und daher an dieser Stelle mit den normalen Zellbezügen arbeiten müssen. Allerdings erweitert sich die Formel automatisch auf den neuen Bereich, wenn weitere Datensätze dazukommen, daher sollte es bei der Funktionalität eigentlich auch keine Einschränkungen geben.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die Anleitung! Das war sehr verständlich und hat mir als Excel-Laiin sehr geholfen.
Bei mir ist aber folgendes Problem aufgetreten:
Ich habe die Werte von drei Spalten verglichen – Titel, Vorname und Nachname. Bei den Titeln sind die Zellen oft nicht befüllt. Mir ist dann aufgefallen, dass bei diese Zeilen nicht erkannt wurde, dass es doppelte Einträge gibt. Ich habe die Formel dann ohne der Titel-Spalte verglichen und eine wesentlich höhere Anzahl an Doppelten erhalten. Woran liegt das? Können leere Felder nicht verglichen werden?
Ich konnte die Frage bei den Kommentaren nicht finden. Falls das schon beantwortet wurde, bitte ich um einen kurzen Hinweis und entschuldige mich für die doppelt gestellte Frage.
Hallo Barbara,
du hast Recht, ZÄHLENWENN verhält sich in diesem Zusammenhang wirklich merkwürdig, leere Zellen werden bei der Zählung ignoriert und damit nicht berücksichtigt. Einen Trick, wie man das umgehen könnte, habe ich spontan leider auch nicht.
Schöne Grüße,
Martin
Hallo Martin,
bei der Suche nach einer Lösung für meine Aufgabenstellung, bin ich über deinen Artikel gestoßen. Leider komme ich damit aber noch nicht zum gewünschten Ergebnis.
Die Tabelle sieht heruntergebrochen wie folgt aus: In Spalte A stehen Namen von Personen, in Spalte B stehen zugeteilte Projekte. In Spalte A gibt es mehrmals die gleichen Namen und in Spalte B gibt es mehrmals die gleichen Projekte, aber komplett gleiche Zeilen sind nicht vorhanden.
Beispiel:
Spalte A – Spalte B
Müller – Projekt 1
Huber – Projekt 1
Müller – Projekt 2
Müller – Projekt 3
Huber – Projekt 4
Huber – Projekt 5
Müller – Projekt 6
Huber – Projekt 6
Meier – Projekt 4
Meier – Projekt 2
Nun möchte ich per Formel oder notfalls per VBA herausfinden, welche beiden Personen am häufigsten zusammengearbeitet haben und wie oft das der Fall war, ohne dabei die Person oder das Projekt vorzugeben. Im Beispiel wäre das gewünschte Ergebnis „Müller, Huber: 2 gemeinsame Projekte“.
Hast du eine Lösung dafür parat?
Vielen Dank und schöne Grüße
Paul
Hallo Paul,
eine sehr interessante und kniffelige Frage. Ich dachte zuerst an eine Pivot-Tabelle. Die bringt etwas mehr Übersicht, aber so richtig bekommt man das gewünschte Ergebnis damit auch nicht heraus. Spontan fällt mir daher keine Lösung dafür ein.
Schöne Grüße,
Martin
Hallo Martin,
ich danke dir dennoch für deine Mühe.
Mittlerweile habe ich einen – zugegebenermaßen etwas komplizierten – Lösungsweg gefunden, komme aber an einer anderen Stelle noch nicht weiter. Hast du dich schon damit beschäftigt, wie man M-Code aus dem Power Query Editor ins VBA-Makro übernimmt? Finde hierzu kaum was Nützliches.
Schöne Grüße
Paul
Hallo Paul,
was genau möchtest du denn machen? Über VBA einen bestimmten M-Code generieren lassen?
Mir sind keine Möglichkeiten bekannt, über VBA auf Power Query zuzugreifen. Außer natürlich vorhandene Abfragen auszuführen.
Schöne Grüße,
Martin
Hallo Martin,
hab es inzwischen geschafft. Auch über VBA lässt sich Power Query „bedienen“.
Meine Vorgehensweise ist nun so, dass ich je Projekt eine Datei habe, darin werden alle Personen-Kombinationen (ohne Duplikate) generiert – hierbei hilft mir Power Query – und das Projekt daneben geschrieben, also in etwa so:
Müller – Huber, Projekt 1
Müller – Meier, Projekt 1
usw.
In einer Master-Datei werden dann all diese einzelnen Listen aus den Projekt-Dateien zusammengetragen und ausgewertet, sodass das gewünschte Ergebnis vorliegt.
Sorry, wenn das hier thematisch fehl am Platz ist. Kann gern gelöscht werden.
Schöne Grüße
Paul
Hallo Paul,
freut mich zu hören, dass du noch eine Lösung für dein Problem gefunden hast.
Schöne Grüße,
Martin