Excel Zählenwenns bedingte Formatierung bei gleichen Einträgen?
Ich habe eine Tabelle mit den Spalten Name und Vorname. Hier möchte ich eine bedingte Formatierung, die doppelte Namen (Vor- und Nachname) farblich kennzeichnet. Bislang habe ich folgende Formel verwendet:
=zählenwenns($F$1:$F$100;$F1;$G$1:$G$100;$G1)>1
Leider werden dabei die Eheleute nicht berücksichtigt. Wie kann ich die Formel anpassen, damit auch die Eheleute berücksichtigt werden?
2 Antworten
Diese (quasi-Matrix-)Formel
=Summenprodukt((F1=F$1:F$100)*(G1=G$1:G$100))>0
sollte Eheleute ausschließen und auch in der bedingten Formatierung funktionieren, hab letzteres aber nicht ausprobiert.
Willst Du zusätzlich Eheleute als Doppel markiert haben, dann
=SUMMENPRODUKT(NICHT(ISTFEHLER(FINDEN($F1&" ";$F$1:$F$100;1)))*NICHT(ISTFEHLER(FINDEN($G1;$G$1:$G$100))))>0
Zwischen den Gänsefüßchen " " steht ein Leerzeichen!
Das funktioniert nur, wenn nach dem gesuchten Vornamen tatsächlich ein Leerzeichen kommt, zB Hans Meier findet Hans & Eva Meier, Hans und Eva, sowie zB Viktor-Hans und Eva Meier (wäre sehr speziell), aber nicht Hans&Eva Meier.
Hans-Dieter Meier stört deshalb nicht.
Aber ein zweiter Hans Meier wird so NICHT gefunden!!!
Diese Formel ist nun schwierig zu kombinieren mit Deiner ZählenwennS- oder der eingangs erwähnten Summenprodukt-Formel, die einen zweiten Hans Meier finden würde.
Du kannst hingegen zwei Bedingungen setzen mit jeweils einer der Formeln und kannst dann per Format dadurch sogar unterscheiden zwischen echten Doppelnennungen (2x Hans Meier) und Eheleuten.(Hans & Eva Meier)
Danke für den *, ich hoffe, Du kannst Damit Deiner Aufgabe auch wirklich nachkommen!
Da ist die Frage, wie Du Eheleute erkennst - nur Nachname gleich oder gibt es weitere Merkmale?
Ich würde es fast als eigene Bedingung anlegen, dann kannst Du überprüfen, ob es richtige Ergebnisse liefert (und die Formatierung dann identisch setzen zur bisherigen Formel)
Die Sache mit den Vornamen ist natürlich wackelig - ich unterstelle, dass es wirklich immer mit UND geschrieben ist:
=WENN(ISTFEHLER(FINDEN("und";F1));ZÄHLENWENNS(F:F;"*"&F1&"*";G:G;G1);ZÄHLENWENNS(F:F;LINKS(F1;FINDEN(" und";F1)-1);G:G;G1)+ZÄHLENWENNS(F:F;RECHTS(F1;FINDEN(" und";F1)-2);G:G;G1)+ZÄHLENWENNS(F:F;F1;G:G;G1))>1
Man muss zwei Fälle unterscheiden:
1. Der Name als Einzelperson: Da kann man mit * Suchen: *Hans* -> da wird die Bedingung >1 erfüllt weil sowohl der eigentlich Hans gefunden wird, also auch der Hans in "Hans und Beate"
-> Restrisiko: Ein Hans Dieter mit gleichem Nachnamen wird auch zugeordnet. Da fällt mir keine direkte Lösung ein, wie man prüfen könnte ob im Treffer ein "und" einhalten ist.
2. Der kombinierte Vorname Hans und Beate: Hier muss man einzeln nach den beiden Vornamen schauen, also links vom ersten Leerzeichen und rechts nach dem "und". Dazu noch der Treffer mit dem vollständig ausgeschriebenen Namen (um den Suchtext selbst auch als Treffer zu erfassen für die Bedingung >1)
Vielen Dank für die schnelle Reaktion,
gleicher Name und Teil des Vornamen. Beispiel: Hans Meier und Hans und Beate Meier.