Duplikate in Excel über mehrere Spalten finden 43

Artikelbild-235
Dubletten über mehrere Spalten mit einer eigenen Formatierungsregel erkennen
 

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

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

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

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

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?

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

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

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

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.

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Annette Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

43 Gedanken zu “Duplikate in Excel über mehrere Spalten finden

  • Avatar-Foto
    Uwe Kuhlenberg

    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?

  • Avatar-Foto
    Uwe Kuhlenberg

    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.

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Uwe Kuhlenberg

        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!

  • Avatar-Foto
    Christopher Methler

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Christian

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Ronja Oden

    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.

      • Avatar-Foto
        Flo Fieb

        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

        • Avatar-Foto
          Martin Weiß

          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

        • Avatar-Foto
          Tilo

          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

          • Avatar-Foto
            Martin Weiß

            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

        • Avatar-Foto
          Sebastian Fox

          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.

  • Avatar-Foto
    Christian Hofmann

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Christian Hofmann

        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    Nicole

    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

  • Avatar-Foto
    Bernhard Spörlein

    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

    • Avatar-Foto
      Martin Weiß

      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:
      Text herauslösen

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Annette

    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!

  • Avatar-Foto
    Jonas

    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

    • Avatar-Foto
      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?

      • Avatar-Foto
        Martin Weiß

        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

  • Avatar-Foto
    Bettina Himpsl

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sabrina

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Anselm

    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??

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Tilman R

    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

    • Avatar-Foto
      Martin Weiß

      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.
      Duplikate erkennen

      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

  • Avatar-Foto
    Peter

    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,

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Christian Hommen

    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

    • Avatar-Foto
      Martin Weiß

      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