7 Wege für eine Liste mit eindeutigen Werten

Namen, Datumslisten, Artikelnummern, … Sich wiederholende Einträge sind Alltag in Excel. Doch oftmals benötigen wir eine Liste mit eindeutigen Einträgen, um bspw. eine Auflistung unserer Mitarbeiter oder die Anzahl unserer Produkte zu erhalten.

Für eine solche Liste müssen sich wiederholende Werte (Duplikate) entfernt werden. Dafür gibt es verschiedene manuelle und automatisierte Methoden, die ich dir in diesem Beitrag vorstelle!

Duplikate manuell entfernen 💪🏻

Benutzeroberfläche (User Interface)

Klicke in die Tabelle, anschließend in der Registerkarte Daten im Bereich Datentools auf Duplikate entfernen.

Es öffnet sich ein Fenster mit den Einstellungen zum Duplikate entfernen. Wähle die Spalten aus, die auf Duplikate geprüft werden sollen.

Möchtest du mehrere Spalten einzeln prüfen, so markierst du nur eine Spalte und wiederholst die Funktion mit den anderen Spalten. Diese Quick-and-Dirty Lösung ist bei der einmaligen Aufbereitung von Daten Gold wert! 🥇

Bedingte Formatierung

Markiere die zu prüfende Spalte und gehe in die Registerkarte Start im Bereich Formatvorlagen auf die Funktion Bedingte Formatierung. Anschließend gehst du in das Untermenü Regeln zum Hervorheben von Zellen -> Doppelte Werte.

Es erscheint ein Fenster, in welchem du die Formatierung der Hervorhebung anpassen kannst.

Nun kannst du die Duplikate filtern & entfernen. Der Vorteil dieser Methode ist die visuelle Darstellung (hier in rot) und die automatische Anwendung auf neue Einträge. Sie bietet sich daher für regelmäßige Sicht-Prüfungen sehr gut an! 👍🏻

Bestimmte Werte entfernen durch Suche

Drücke die Tastenkombination STRG + F oder gehe über die Registerkarte Start im Bereich Bearbeiten in die Funktion Suchen und Auswählen -> Suchen.

Es öffnet sich ein neues Fenster. Gib im Feld Suchen nach den gewünschten Wert ein und drücke anschließend auf Alle suchen.
Nun erscheinen unterhalb alle Zellen mit dem gesuchten Wert.

Drücke STRG + A oder Shift + linke Maustaste und wähle damit alle Einträge aus.

Schließe nun das Fenster (Esc), die Zellen sollten weiterhin markiert sein (grau hinterlegt).

Nun kannst du die Zellen nach Belieben ändern, bspw. löschen (STRG + Minus-Taste), leeren (Entf) oder überschreiben (STRG + Enter).

Diese Methode bietet sich für einmalige Fälle an, in welchen du keine permanente bedingte Formatierung möchtest, dir die Duplikate entfernen-Funktion der Benutzeroberfläche jedoch nicht die gewünschte Lösung bringt.

Das Entfernen von Duplikaten automatisieren ♻

EINDEUTIG-Funktion

Die EINDEUTIG-Funktion ist eine der neuen Array-Formeln, die in Excel 2021 eingeführt wurden. Array bedeutet, dass EINDEUTIG mit nur einer Formeleingabe die Berechnung für die gesamte Matrix (in unserem Fall die Spalte) durchführt. Wir benötigen also nur eine Formel!

EINDEUTIG weist drei Argumente auf, wovon 2 optional sind. Als erforderliches Argument benötigen wir die Matrix, in diesem Fall die Spalte KundenID.

=EINDEUTIG(verkäufe_eindeutig[KundenID])

Das zweite Argument [nach_Spalte] (optional) akzeptiert WAHR (1) oder FALSCH (0). FALSCH sucht vertikal (Zeilen), WAHR horizontal (Spalten).

Das dritte Argument [genau_einmal] (optional) akzeptiert ebenfalls WAHR / FALSCH. FALSCH gibt alle Werte einmalig zurück. WAHR gibt dir nur Werte zurück, die exakt einmal vorkommen.

Lässt du die zwei optionalen Argumente weg, so nimmt die Formel für die optionalen Parameter FALSCH an. Perfekt zum Duplikate entfernen 🙂

Matrix-Formel (Index / Vergleich)

Mit der EINDEUTIG-Funktion kann man sich schnell & einfach eine einzigartige Liste erzeugen. Doch diese Funktion ist erst ab Excel 2021 vorhanden.

Verwendest du eine frühere Excel-Version, so kannst du auf diese Matrix-Formel zugreifen. Diese musst du so lange kopieren, bis du keinen Wert (-) mehr erhältst.

=WENNNV(
INDEX(
verkäufe_matrix[KundenID];
VERGLEICH(0;ZÄHLENWENN(I$1:$I1;verkäufe_matrix[KundenID]);0)
);"-")

Zu beachten ist besonders der Bezug in der ZÄHLENWENN-Formel (I$1:$I1). Dieser beginnt eine Zelle über der eigentlichen Formel und erweitert sich durch die Setzung der $-Zeichen entsprechend beim nach unten kopieren.

Diese Formel habe ich bei Martin Weiß gefunden, weitere Infos gibt es hier.

PivotTable

Suche in der Registerkarte Einfügen unter Tabellen den PivotTable Button und klicke auf das Icon. Wähle nun den Datenbereich aus.

Alternativ kannst du in eine intelligente Tabelle klicken und in der Registerkarte Tabellenentwurf unter Tools die Funktion Mit PivotTable zusammenfassen nutzen. Diese wählt automatisch die intelligente Tabelle als Bereich aus.

Ziehe die Spalte, welche die eindeutigen Werte beinhalten soll, in die Zeilen des PivotTable.

Nun hast du eine Auflistung aller eindeutigen Einträge der Spalte und somit alle Duplikate entfernt! 🙂 Ein netter Nebeneffekt beim PivotTable: Wenn möglich sind die Werte gleich sortiert!

Power Query

Mit Power Query kannst du dir ebenso eine eindeutige Liste ausgeben lassen, auch im Tabellenformat. Lade hierfür die Daten in Power Query.

Anschließend wählst du die gewünschte Spalte aus, mit Rechtsklick auf andere Spalten entfernen bleibt die gewünschte Spalte übrig.

Klicke erneut auf die Spalte und wähle den Befehl Duplikate entfernen, dadurch hast du eine eindeutige Liste erzeugt!

Abschließend kannst du die Werte zur besseren Übersichtlichkeit sortieren.

Nun kannst du die Abfrage schließen & in ein Blatt laden.

Wähle aus, in welcher Form du die Liste haben möchtest. Entweder als intelligente Tabelle oder als PivotTable. Meine Empfehlung wäre eine Tabelle, für ein PivotTable gibt es eine einfachere Lösung.

Power Query bietet den Vorteil, dass die Daten wieder als intelligente Tabelle geladen werden können, statt als PivotTable oder Formel. Zudem können weitaus komplexere Sachverhalte hiermit gelöst werden.

Fazit 💡

In diesem Beitrag habe ich dir einige Methoden gezeigt, um eine Auflistung eindeutiger Werte zu erhalten.

Jede dieser Methoden hat ihre eigenen Vorteile hinsichtlich Anwendungsfreundlichkeit oder Flexibilität und jede hat ihre eigenen Anwendungsfälle.

Die manuellen Vorgehensweisen bieten sich für schnelle & einmalige Lösungen an, PivotTable & Power Query für automatisierte & häufige Vorgänge.

Da sollte auch für dich die ein oder andere dabei sein. 😉
Viel Spaß beim Ausprobieren!

Beispieldatei zum Herunterladen