Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1528to1532
1528to1532
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Fam.-Verbund (gleicher Name, gleiche Straße)

Forumthread: Fam.-Verbund (gleicher Name, gleiche Straße)

Fam.-Verbund (gleicher Name, gleiche Straße)
24.12.2016 15:59:47
Christoph
Hallo zusammen,
folgendes Problem konnte ich bislang nicht lösen:
In einer Tabelle mit versch. Personen (teilweise Personenverbünde) sollen Fam.-Verbünde in Spalte H gekennzeichnet werden (dies ist dann der Fall, wenn die Person bzw. der Verbund in der gleichen Straße wohnt und der Nachname von Mann oder der Frau weiter oben bereits vorkommt - Beispiel Eva Maria Müller & Thomas Müller). Kennzeichnung soll in Spalte H mit "Verbund" erfolgen.
Hinweis: Die rote Schriftart habe ich nur zum besseren Verständnis verwendet.
Vielen Dank für eure Hilfe.
Christoph
Mitglieder

 ABCDEFGH
1VornameNachnameNamenzusatzStraßeGanzer VornameGanzer NachnameNamen (final)Dublikat in Name & Straße
2FranzMüllerLudmilla Müller-MaierStraße 1Ludmilla Müller-MaierFranz Müller u. Ludmilla Müller-Maier 
3ThomasWeberEva Maria MüllerStraße 1Eva Maria MüllerThomas Weber u. Eva Maria MüllerVerbund
4MarkusMüller Straße 1  Markus MüllerVerbund
5HansMüllerProf. Dr. Elke WeberStraße 2Prof. Dr. Elke WeberHans Müller u. Prof. Dr. Elke Weber 
6Ludwigvan BeethovenMatthilde MaierStraße 1Matthilde MaierLudwig van Beethoven u. Matthilde Maier 

Formeln der Tabelle
ZelleFormel
E2=WENN(C2="";"";LINKS(C2;VERWEIS(999;SUCHEN(" ";GLÄTTEN(C2); SPALTE(A$1:IG$1)))-SUMME(MMULT((WECHSELN(C2;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>C2)*{4;4;4;4;3;3};1))))
F2=WENN(C2="";"";RECHTS(C2;LÄNGE(C2)-LÄNGE(E2)))
G2=A2&WENN(B2<>F2;" "&B2;"")&WENN(E2<>"";" u. ";"")&E2&WENN(B2=F2;B2;"")&WENN(B2<>F2;F2;"")
E3=WENN(C3="";"";LINKS(C3;VERWEIS(999;SUCHEN(" ";GLÄTTEN(C3); SPALTE(A$1:IG$1)))-SUMME(MMULT((WECHSELN(C3;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>C3)*{4;4;4;4;3;3};1))))
F3=WENN(C3="";"";RECHTS(C3;LÄNGE(C3)-LÄNGE(E3)))
G3=A3&WENN(B3<>F3;" "&B3;"")&WENN(E3<>"";" u. ";"")&E3&WENN(B3=F3;B3;"")&WENN(B3<>F3;F3;"")
E4=WENN(C4="";"";LINKS(C4;VERWEIS(999;SUCHEN(" ";GLÄTTEN(C4); SPALTE(A$1:IG$1)))-SUMME(MMULT((WECHSELN(C4;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>C4)*{4;4;4;4;3;3};1))))
F4=WENN(C4="";"";RECHTS(C4;LÄNGE(C4)-LÄNGE(E4)))
G4=A4&WENN(B4<>F4;" "&B4;"")&WENN(E4<>"";" u. ";"")&E4&WENN(B4=F4;B4;"")&WENN(B4<>F4;F4;"")
E5=WENN(C5="";"";LINKS(C5;VERWEIS(999;SUCHEN(" ";GLÄTTEN(C5); SPALTE(A$1:IG$1)))-SUMME(MMULT((WECHSELN(C5;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>C5)*{4;4;4;4;3;3};1))))
F5=WENN(C5="";"";RECHTS(C5;LÄNGE(C5)-LÄNGE(E5)))
G5=A5&WENN(B5<>F5;" "&B5;"")&WENN(E5<>"";" u. ";"")&E5&WENN(B5=F5;B5;"")&WENN(B5<>F5;F5;"")
E6=WENN(C6="";"";LINKS(C6;VERWEIS(999;SUCHEN(" ";GLÄTTEN(C6); SPALTE(A$1:IG$1)))-SUMME(MMULT((WECHSELN(C6;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>C6)*{4;4;4;4;3;3};1))))
F6=WENN(C6="";"";RECHTS(C6;LÄNGE(C6)-LÄNGE(E6)))
G6=A6&WENN(B6<>F6;" "&B6;"")&WENN(E6<>"";" u. ";"")&E6&WENN(B6=F6;B6;"")&WENN(B6<>F6;F6;"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anzeige

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Fam.-Verbund (gleicher Name, gleiche Straße)
24.12.2016 16:35:25
Hajo_Zi
das Tool hat keine Rückwandelfunktion, darum ohne Formeln.
Ich bin nicht der Formelmensch. Darum mit Hilfsspalte.

Tabelle2
 ABCDEFGHIJ
1VornameNachnameNamenzusatzStraßeGanzer VornameGanzer NachnameNamen (final)Dublikat in Name & StraßeSpalteB/D 
2FranzMüllerLudmilla Müller-MaierStraße 1LudmillaMüller-MaierFranz Müller u. Ludmilla Müller-Maier Müller-MaierStraße 1Müller-MaierStraße 1
3ThomasWeberEva Maria MüllerStraße 1Eva MariaMüllerThomas Weber u. Eva Maria MüllerVerbundWeberStraße 1MüllerStraße 1
4MarkusMüller Straße 1  Markus MüllerVerbundMüllerStraße 1Straße 1
5HansMüllerProf. Dr. Elke WeberStraße 2Prof. Dr. ElkeWeberHans Müller u. Prof. Dr. Elke Weber MüllerStraße 2WeberStraße 2
6Ludwigvan BeethovenMatthilde MaierStraße 1MatthildeMaierLudwig van Beethoven u. Matthilde Maier van BeethovenStraße 1MaierStraße 1
die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt
Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich N/A
I2=WENN(SUCHEN(B2;F2)>0;F2;B2)&D2  
I3:I6=B3&D3  
J2:J6=F2&D2  

Bedingte Formatierung Haupttabelle 1  
Wird angewendet auf   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ Bereich
$A:$H01.Bed.: Formel ist =ZÄHLENWENN($I$2:$I$6;$J1)+ZÄHLENWENN($J$2:$J$6;$I1)=1  15773696         2$A:$H
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich    
Wird angewendet auf   Nr. Bed.  Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
$A:$H01.Bed.   Falsch          2$A:$H
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


Füllfarben und Muster  
Zelle Rot Grün Blau Color Muster Farbe
A1:J6 25525525516777215   
Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate  
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
A1: D1 0 0 0 0 Fett         
A4: D4, E3:F3             
E1:F1, H1:J1 79 129 189 12419407 Fett         
G1 255 0 0 255 Fett         
Zellen mit Schriftformatierung automatisch werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 24.04 einschl. 64 Bit



Anzeige
AW: Fam.-Verbund (gleicher Name, gleiche Straße)
24.12.2016 17:45:47
Christoph
Hallo Hajo_Zi,
danke für die Hilfe. Nur leider löst das nicht mein Problem. Zunächst habe die Formel für die bedingte Formatierung in Spalte K eingefügt (benutzerdefinierte Formatierung ist als Unterstützung top, aber wird später nicht benötigt). Das eigentliche Problem ist jedoch, dass die Formel nur für Müller angewendet wird. Wenn ich in Zeile 5 Hans Müller zu Hans van Beethoven ändere und in Zeile 6 Straße 1 zu Straße 2, sollte K6 ebenfalls WAHR anzeigen (beide van Beethoven & Straße 2). Da die Liste mehrere hundert Personen enthält soll dies natürlich auch für die anderen Personenverbünde gelten. Vielleicht ist in diesem Zusammenhang noch interessant, dass die Tabelle später in unterschiedlichster Weise gefiltert wird (Kriterien werden später noch eingefügt). Die Formeln sollten daher "dynamisch" sein (Formel in I3 und I3 sollten vom Stamm her gleich sein).
Danke nochmals,
Gruß Christoph
Anzeige
AW: Fam.-Verbund (gleicher Name, gleiche Straße)
24.12.2016 18:22:40
Hajo_Zi
Hallo Christoph,
Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.
Ein Nachbau sieht meist anders aus als das Original.
Der Name einer hochgeladenen Mappe wird im Beitrag automatisch angezeigt, sodass es bei Verwendung von aussagekräftigen Namen leichter fällt, sie später im Ablageordner wiederzufinden und sie gedanklich einem bestimmten Thema zuzuordnen. Namen wie Test, Mappe, Beispiel usw. sind so allgemein, dass eine Zuordnung zu einem Thema unmöglich gemacht wird.
Gruß Hajo
Anzeige
AW: Fam.-Verbund (gleicher Name, gleiche Straße)
24.12.2016 19:14:41
Hajo_Zi
es ist Weihnachten.

Tabelle2
 ABCDEFGHI
1VornameNachnameNamenzusatzStraßeGanzer VornameGanzer NachnameNamen (final)Dublikat in Name & StraßeSpalteB/D/F
2FranzMüllerLudmilla Müller-MaierStraße 1LudmillaMüller-MaierFranz Müller u. Ludmilla Müller-Maier Müller-MaierStraße 1
3ThomasWeberEva Maria MüllerStraße 1Eva MariaMüllerThomas Weber u. Eva Maria MüllerVerbundMüllerStraße 1
4MarkusMüller Straße 1  Markus MüllerVerbundMüllerStraße 1
5HansMüllerProf. Dr. Elke WeberStraße 1Prof. Dr. Elkevan BeethovenHans Müller u. Prof. Dr. Elke Weber van BeethovenStraße 1
6Ludwigvan BeethovenMatthilde MaierStraße 1Matthilde Ludwig van Beethoven u. Matthilde Maier van BeethovenStraße 1
die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
werden in dieser Tabelle nicht dargestellt
Schriftart wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich N/A
I2:I6=WENN(ISTZAHL(SUCHEN(B2;F2));F2;WENN(UND(B2<>F2;F2<>"");F2;B2))&D2  

Bedingte Formatierung Haupttabelle 1  
Wird angewendet auf   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ Bereich
$A:$H01.Bed.: Formel ist =ZÄHLENWENN($I$1:$I$6;$I1)>1  15773696         2$A:$H
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich    
Wird angewendet auf   Nr. Bed.  Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
$A:$H01.Bed.   Falsch          2$A:$H
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


Füllfarben und Muster  
Zelle Rot Grün Blau Color Muster Farbe
A1:I6 25525525516777215   
Zellen mit Füllfarbe automatisch werden nicht dargestellt

Schriftformate  
Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
A1: D1 0 0 0 0 Fett         
A4: D4, E3:F3             
E1:F1, H1:I1 79 129 189 12419407 Fett         
G1 255 0 0 255 Fett         
Zellen mit Schriftformatierung automatisch werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 24.04 einschl. 64 Bit


Gruß Hajo
Anzeige
AW: unklar, denn ...
25.12.2016 11:18:41
...
Hallo Christoph,
... warum z.B. gehört Markus Müller nicht (auch oder nur) zu Franz Müller? Wie soll das durch Deine bisherigen Angaben eindeutig erkannt werden?
Gruß Werner
.. , - ...
AW: unklar, denn ...
25.12.2016 22:09:02
Christoph
Hallo zusammen,
wünsche euch ebenfalls schöne Weihnachten. Zunächst Danke an dich Hajo - du hast mir mit der Formel zur Kombination von Name & Straße als Hilfsspalte den richtigen Ansatz geliefert, mit der ich eine Lösung basteln konnte (Vollständigkeitshalber: Die Formel in deinem 3. Beitrag für I2 selbst brachte allerdings nicht die erhofte Lösung). Komplette Lösung habe ich unten eingefügt.
@Werner: Eine 100% Identifikation ist natürlich anhand von nur zwei Kriterien (Nachname, Straße) nicht möglich. Mir geht es aber auch gar nicht darum, sondern dass ein möglicher Verbund lieber einmal zuviel gekennzeichnet wird. Ein Verbund ist sehr wahrscheinlich, wenn jmd. mit dem selben Nachnamen im gleichen Haus wohnt (z.B. Franz Müller, Eva Maria Müller & Markus Müller -> Eva & Markus könnten z.B. Kinder von Franz sein). Einzigste Ausnahme die mir gerade einfällt, wäre, wenn zwei Müller in einem Mehrfamilienhaus wohnen, aber dennoch nicht mit einander verwandt sind.
Ein Frage hätte ich noch 1) Lässt sich meine Lösung vereinfachen, hinsichtlich großer Datenbestand ?
---Da bin ich noch dran---
Bei der Erstellung des finalen, statischen Datenbestandes (bestehend aus Vollständiger Personenverbund, Straße & Dublikatsindikator) hilft mir meine obige Lösung. Richtig genial wäre jetzt noch, wenn ich erst den Vollständigen Namen des Pesonenverbunds zum Schluss durch Einfügen als Wert stehen lassen könnte, ich die Hilfsspalten (Spalte D-H) zur Bildung des vollständigen Namens nicht mehr benötige, dabei aber die Dublikate-Funktion Ihre Werte aus der vollständigen Namensspalte (Spalte A) zieht.
1. "Virtuelle" Zerstückelung der Spalte A in seine Bestandteile, d.h. ohne Hilfsspalte (sollten möglich sein mit Teilen-Funktion, hier benötige ich keine Hilfe)
2. Um zu prüfen ob eine NamenStraße-Kombination mehrfach vorkommt, habe ich bislang auf die Hilfsspalten I & J zurückgegriffen. Mein Problem Spalte K & L greifen ja auf diesen Bereich zu (ZÄHLENWENN($I$3:$J$10;$I3)). Ich würde gern allerdings I & J in K & L integrieren. Komme hier allerdings nicht weiter, habt ihr mir vielleicht einen Lösungsansatz ? (Benötige ich deshalb, um auch später die Dublikate dynamisch kennzeichnen zu können und nicht nur zu Beginn)
Gruß Christoph

Mitglieder

































































































































































  A B C D E F G H I J K L
1 Sichtbarer finaler Datenbestand Unbearbeiteter Datensatz / Quelle Hilfsspalten
2 Vollständiger Name Straße Dublikat Vorname Nachname Namenzusatz Ganzer Vorname Ganzer Nachname Spalte B/D Spalte F / D D1 D2
3 Markus Ostermann & Tatjana Schön Straße 1 N2/2 Markus Ostermann Tatjana Schön Tatjana Schön OstermannStraße 1 SchönStraße 1 1 2
4 Georg Müller & Sabine Schön Straße 1 N1/4 N2/2 Georg Müller Sabine Schön Sabine Schön MüllerStraße 1 SchönStraße 1 4 2
5 Anton Müller & Ludmilla Müller-Maier Straße 1 N1/4 Anton Müller Ludmilla Müller-Maier Ludmilla Müller-Maier MüllerStraße 1   4 0
6 Thomas Weber & Eva Maria Müller Straße 1 N2/4 Thomas Weber Eva Maria Müller Eva Maria Müller WeberStraße 1 MüllerStraße 1 1 4
7 Bernd Müller Straße 1 N1/4 Bernd Müller       MüllerStraße 1   4 0
8 Ludwig & Prof. Dr. Elke van Beethoven Straße 2 N1/3 Ludwig van Beethoven Prof. Dr. Elke van Beethoven Prof. Dr. Elke van Beethoven van BeethovenStraße 2   3 0
9 Ludwig jun. Mozart & Anna van Beethoven Straße 2 N2/3 Ludwig jun. Mozart Anna van Beethoven Anna van Beethoven MozartStraße 2 van BeethovenStraße 2 1 3
10 Lea van Beethoven Straße 2 N1/3 Lea van Beethoven       van BeethovenStraße 2   3 0

 











Formeln der Tabelle












































Zelle Formel
A3 ~f~=D3&WENN(E3<>H3;" "&E3;"")&WENN(G3<>"";" & ";"")&G3&WENN(E3=H3;E3;"")&WENN(E3<>H3;H3;"")~f~
C3 
~f~=WENN(UND(K3>1;L3>1);"N1/"&K3&" N2/"&L3;WENN(K3>1;"N1/"&K3;WENN(L3>1;"N2/"&L3;"")))~f~

G3 
~f~=WENN(F3="";"";LINKS(F3;VERWEIS(999;SUCHEN(" ";GLÄTTEN(F3);SPALTE(B$2:IK$2)))-SUMME(MMULT((WECHSELN(F3;{"von ";"van ";"und ";"der ";"zu ";"ob "};)<>F3)*{4;4;4;4;3;3};1))))~f~

H3 
~f~=WENN(F3="";"";RECHTS(F3;LÄNGE(F3)-LÄNGE(G3)))~f~

I3 
~f~=E3&B3~f~

J3 
~f~=WENN(ODER(ISTZAHL(SUCHEN(E3;H3));H3="");"";H3&B3)~f~

K3 
~f~=ZÄHLENWENN($I$3:$J$10;$I3)~f~

L3 
~f~=WENN(J3="";0;ZÄHLENWENN($I$3:$J$10;$J3))~f~

   




Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8


Anzeige
AW: unklar, denn ...
25.12.2016 23:58:09
Christoph
Der Teil ab "Da bin ich noch dran" hat sich quasi erledigt. Die Hilfsspalten werde ich auch in der finalen Datei beibehalten, allerdings sollte wie oben beschrieben die Nachnamen aus der Spalte mit den vollständigen Namensverbünden extrahiert werden.
Formeln mit Rechts(), Links() & Suchen() etc. zu erstellen ist allerdings schwieriger als gedacht :( Vom Ansatz her müsste es wie folgt passen, nur bekomm ich das nicht in einer funktionierenden Formel unter. Achja bei den Einzelnamen werde ich einfach ein Wenn(ISTZAHL(SUCHEN("&";B3)) davorstellen, um die Zeile auf ein &-Zeichen zu prüfen und nicht vom &-Zeichen aus suchen, sondern sondern nur von rechts.
Nachname 1: Von Rechts nach dem &-Zeichen bis zum Leerzeichen
Nachname 2: Von Rechts bis zum ersten Leerzeichen
Wäre super, wenn ihr mir dabei nochmal etwas unter die Arme greifen könntet. Danke :)
Vollständer Name aufteilen

 BCD
2Vollständiger NameNachname 1Nachname 2
3Markus Ostermann & Tatjana Schön  
4Georg Müller & Sabine Schön  
5Anton Müller & Ludmilla Müller-Maier  
6Thomas Weber & Eva Maria Müller  
7Bernd Müller  
8Ludwig & Prof. Dr. Elke van Beethoven  
9Ludwig jun. Mozart & Anna van Beethoven  
10Lea van Beethoven  


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anzeige
Vorname Nachname nach &
26.12.2016 05:41:45
WF
Hi,
zuerst in D3:
=TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(X$1:X$99)))+1;9^9)
dann in C3:
=TEIL(WECHSELN(A3;" "&D3;);FINDEN("&";WECHSELN(A3;" "&D3;))+2;99)
beide runterkopieren
Bei einem Einzelnamen ohne & ist der Nachname in Spalte D OK
in Spalte C gibt's #WERT!
das wäre dann lediglich:
=WECHSELN(A3;" "&D3;)
Die Ausnahme musst Du oben noch reinfummeln.
Salut WF
Anzeige
AW: Vorname Nachname nach &
26.12.2016 13:09:35
Christoph
Hallo WF,
danke für die Formeln. Ich benötige allerdings nicht Vor- und Nachname, sondern Nachname 1 z.B. Mozart und Nachname 2 z.B. Beethoven. Sorry für die Umstände, kannst du mir nochmal helfen ? Um keine unnötigen Schwierigkeiten aufzubauen, werde ich die Syntax bei gleichem Familiennamen (Zeile 8) zu Ludwig VAN BEETHOVEN & Prof. Dr. Elke van Beethoven ändern (könnten ja theoretisch mehrere Vornamen sein und so ist nicht zu erkennen, ob es sich um Vorname oder Nachname handelt).
Muss ich die "9^9" bei mehr Einträgen anpassen ? Was passiert in Spalte Y$1:Y$99 ?
=TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(Y$1:Y$99)))+1;9^9)
Gruß,
Christoph
Anzeige
die 2 Nachnamen
26.12.2016 16:06:05
WF
Hi,
dann in C3:
=WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3)-2);VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3)-2); ZEILE(X$1:X$99)))+1;9^9);TEIL(A3;FINDEN(" ";A3)+1;99))
und in D3;
=WENN(ISTZAHL(FINDEN("&";A3));TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(X$1:X$99)))+1;9^9);"")
beide runterkopieren
Zu Einzelnamen hatte ich keine Lust mehr.
9^9 bleibt - das muss nur ne große Zahl sein
Spalte(X$1:X$99) ist nur ein Zähler - kannst auch Spalte(F$1:F$99) nehmen
WF
Anzeige
ist nur ein WENNFEHLER mehr
26.12.2016 16:21:28
WF
in C3:
=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3)-2);VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3) -2);ZEILE(X$1:X$99)))+1;9^9);TEIL(A3;FINDEN(" ";A3)+1;99));A3)
WF
die 2 Nachnamen (1x mit van / 1x ohne)
26.12.2016 16:35:47
Christoph
Hallo WF,
wow danke. Wär ich wohl nicht drauf gekommen... Ist mir schon fast unangenehm, allerdings passt es leider nocht nicht ganz. Es sollte immer "Beethoven" sein und nicht einmal "van Beethoven", weil sonst kann ich die Zellen später nicht auf Dublikate prüfen.
Gruß,
Christoph
Vollständiger Name zu Nachname1 und Nachname 2

 ABC
3Markus Ostermann & Tatjana SchönOstermannSchön
4Georg Müller & Sabine Mareike SchönMüllerSchön
5Anton Müller & Ludmilla Müller-MaierMüllerMüller-Maier
6Thomas Weber & Eva Maria MüllerWeberMüller
7Bernd MozartMozart 
8Ludwig van Beethoven & Prof. Dr. Elke van BeethovenBeethovenBeethoven
9Ludwig jun. Mozartjun. Mozart 
10Lea van Beethovenvan Beethoven 

Formeln der Tabelle
ZelleFormel
B3=WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A3;FINDEN(" ";A3)+1;99))
C3=WENN(ISTZAHL(FINDEN("&";A3)); TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(X$1:X$99)))+1;9^9); "")
B4=WENNFEHLER(TEIL(LINKS(A4;FINDEN("&";A4)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A4;FINDEN("&";A4)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A4;FINDEN(" ";A4)+1;99))
C4=WENN(ISTZAHL(FINDEN("&";A4)); TEIL(A4;VERWEIS(9^9;FINDEN(" ";A4;ZEILE(X$1:X$99)))+1;9^9); "")
B5=WENNFEHLER(TEIL(LINKS(A5;FINDEN("&";A5)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A5;FINDEN("&";A5)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A5;FINDEN(" ";A5)+1;99))
C5=WENN(ISTZAHL(FINDEN("&";A5)); TEIL(A5;VERWEIS(9^9;FINDEN(" ";A5;ZEILE(X$1:X$99)))+1;9^9); "")
B6=WENNFEHLER(TEIL(LINKS(A6;FINDEN("&";A6)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A6;FINDEN("&";A6)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A6;FINDEN(" ";A6)+1;99))
C6=WENN(ISTZAHL(FINDEN("&";A6)); TEIL(A6;VERWEIS(9^9;FINDEN(" ";A6;ZEILE(X$1:X$99)))+1;9^9); "")
B7=WENNFEHLER(TEIL(LINKS(A7;FINDEN("&";A7)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A7;FINDEN("&";A7)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A7;FINDEN(" ";A7)+1;99))
C7=WENN(ISTZAHL(FINDEN("&";A7)); TEIL(A7;VERWEIS(9^9;FINDEN(" ";A7;ZEILE(X$1:X$99)))+1;9^9); "")
B8=WENNFEHLER(TEIL(LINKS(A8;FINDEN("&";A8)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A8;FINDEN("&";A8)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A8;FINDEN(" ";A8)+1;99))
C8=WENN(ISTZAHL(FINDEN("&";A8)); TEIL(A8;VERWEIS(9^9;FINDEN(" ";A8;ZEILE(X$1:X$99)))+1;9^9); "")
B9=WENNFEHLER(TEIL(LINKS(A9;FINDEN("&";A9)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A9;FINDEN("&";A9)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A9;FINDEN(" ";A9)+1;99))
C9=WENN(ISTZAHL(FINDEN("&";A9)); TEIL(A9;VERWEIS(9^9;FINDEN(" ";A9;ZEILE(X$1:X$99)))+1;9^9); "")
B10=WENNFEHLER(TEIL(LINKS(A10;FINDEN("&";A10)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A10;FINDEN("&";A10)-2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A10;FINDEN(" ";A10)+1;99))
C10=WENN(ISTZAHL(FINDEN("&";A10)); TEIL(A10;VERWEIS(9^9;FINDEN(" ";A10;ZEILE(X$1:X$99)))+1;9^9); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anzeige
AW: die 2 Nachnamen (1x mit van / 1x ohne)
26.12.2016 17:15:11
Christoph
Liegt wohl daran, dass quasi das Leerzeichen von links gesucht wird. Bei all diesen 4 Beispielen sollte immer Beethoven das Ergebnis sein (unabhängig von den Leerzeichen, mehreren Vornamen oder einem Nachnamenspräfix)
Vollständiger Name zu...

 ABC
2Vollständiger NameNachname 1Nachname 2
3Markus Ostermann & Tatjana SchönOstermannSchön
4Georg Müller & Sabine Mareike SchönMüllerSchön
5Anton Müller & Ludmilla Müller-MaierMüllerMüller-Maier
6Thomas Weber & Eva Maria MüllerWeberMüller
7Bernd Maximilian Theodor BeethovenMaximilian Theodor Beethoven 
8Ludwig van Beethoven & Prof. Dr. Elke van BeethovenBeethovenBeethoven
9Ludwig Peter jun. BeethovenPeter jun. Beethoven 
10Lea van Beethovenvan Beethoven 

Formeln der Tabelle
ZelleFormel
B3=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A3;FINDEN(" ";A3)+1;99)); A3)
C3=WENN(ISTZAHL(FINDEN("&";A3)); TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(X$1:X$99)))+1;9^9); "")
B4=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A4;FINDEN("&";A4)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A4;FINDEN("&";A4) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A4;FINDEN(" ";A4)+1;99)); A4)
C4=WENN(ISTZAHL(FINDEN("&";A4)); TEIL(A4;VERWEIS(9^9;FINDEN(" ";A4;ZEILE(X$1:X$99)))+1;9^9); "")
B5=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A5;FINDEN("&";A5)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A5;FINDEN("&";A5) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A5;FINDEN(" ";A5)+1;99)); A5)
C5=WENN(ISTZAHL(FINDEN("&";A5)); TEIL(A5;VERWEIS(9^9;FINDEN(" ";A5;ZEILE(X$1:X$99)))+1;9^9); "")
B6=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A6;FINDEN("&";A6)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A6;FINDEN("&";A6) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A6;FINDEN(" ";A6)+1;99)); A6)
C6=WENN(ISTZAHL(FINDEN("&";A6)); TEIL(A6;VERWEIS(9^9;FINDEN(" ";A6;ZEILE(X$1:X$99)))+1;9^9); "")
B7=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A7;FINDEN("&";A7)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A7;FINDEN("&";A7) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A7;FINDEN(" ";A7)+1;99)); A7)
C7=WENN(ISTZAHL(FINDEN("&";A7)); TEIL(A7;VERWEIS(9^9;FINDEN(" ";A7;ZEILE(X$1:X$99)))+1;9^9); "")
B8=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A8;FINDEN("&";A8)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A8;FINDEN("&";A8) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A8;FINDEN(" ";A8)+1;99)); A8)
C8=WENN(ISTZAHL(FINDEN("&";A8)); TEIL(A8;VERWEIS(9^9;FINDEN(" ";A8;ZEILE(X$1:X$99)))+1;9^9); "")
B9=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A9;FINDEN("&";A9)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A9;FINDEN("&";A9) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A9;FINDEN(" ";A9)+1;99)); A9)
C9=WENN(ISTZAHL(FINDEN("&";A9)); TEIL(A9;VERWEIS(9^9;FINDEN(" ";A9;ZEILE(X$1:X$99)))+1;9^9); "")
B10=WENNFEHLER(WENNFEHLER(TEIL(LINKS(A10;FINDEN("&";A10)-2); VERWEIS(9^9;FINDEN(" ";LINKS(A10;FINDEN("&";A10) -2); ZEILE(X$1:X$99)))+1;9^9); TEIL(A10;FINDEN(" ";A10)+1;99)); A10)
C10=WENN(ISTZAHL(FINDEN("&";A10)); TEIL(A10;VERWEIS(9^9;FINDEN(" ";A10;ZEILE(X$1:X$99)))+1;9^9); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anzeige
dann also wie Nachname 2
26.12.2016 18:06:39
WF
C3:
=WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3&"&")-2);VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3&"&")-2) ;ZEILE(X$1:X$99)))+1;9^9);A3)
WF
Nachname abgeschnitten
26.12.2016 20:03:35
Christoph
Danke WF,
nur irgendwo ist der Fehler drinn. Wenn ich deine Formel nehme, wird bei einer einzelnen Person der letzte Buchstabe des Nachnamens abgeschnitten.
Eine Änderung von FINDEN("&";A2&"&")-2) zu FINDEN("&";A2&"&")-1) gibt zwar optisch eine scheinbar richtige Lösung zurück, hängt dann allerdings bei Ostermann ein Leerzeichen an und es ist wiederum kein Erkennen von Dublikaten möglich. Was muss ich ändern ?
Finaler Stand

 ABCDEFGHI
1Vollständiger NameStraßeNachname 1Nachname 2Hilfsspalte 1Hilfsspalte 2   
2Markus Ostermann & Tatjana SchönStraße 1OstermannSchönOstermannStraße 1SchönStraße 111 
3Ludwig Peter jun. BeethovenStraße 1Beethove BeethoveStraße 1 10 

Formeln der Tabelle
ZelleFormel
C2=WENNFEHLER(TEIL(LINKS(A2;FINDEN("&";A2&"&")-2); VERWEIS(9^9;FINDEN(" ";LINKS(A2;FINDEN("&";A2&"&")-2); ZEILE(T$1:T$98)))+1;9^9); A2)
D2=WENN(ISTZAHL(FINDEN("&";A2)); TEIL(A2;VERWEIS(9^9;FINDEN(" ";A2;ZEILE(T$1:T$98)))+1;9^9); "")
E2=C2&B2
F2=WENN(ODER(ISTZAHL(SUCHEN(C2;D2)); D2=""); "";D2&B2)
G2=ZÄHLENWENN($E$2:$F$9;E2)
H2=WENN(F2="";0;ZÄHLENWENN($E$2:$F$9;F2))
I2=WENN(UND(G2>1;H2>1); "N1/"&G2&" N2/"&H2;WENN(G2>1;"N1/"&G2;WENN(H2>1;"N2/"&H2;"")))
C3=WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3&"&")-2); VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3&"&")-2); ZEILE(T$1:T$98)))+1;9^9); A3)
D3=WENN(ISTZAHL(FINDEN("&";A3)); TEIL(A3;VERWEIS(9^9;FINDEN(" ";A3;ZEILE(T$1:T$98)))+1;9^9); "")
E3=C3&B3
F3=WENN(ODER(ISTZAHL(SUCHEN(C3;D3)); D3=""); "";D3&B3)
G3=ZÄHLENWENN($E$2:$F$9;E3)
H3=WENN(F3="";0;ZÄHLENWENN($E$2:$F$9;F3))
I3=WENN(UND(G3>1;H3>1); "N1/"&G3&" N2/"&H3;WENN(G3>1;"N1/"&G3;WENN(H3>1;"N2/"&H3;"")))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anzeige
Leerzeichen schon - aber woanders
26.12.2016 20:21:00
WF
=WENNFEHLER(TEIL(LINKS(A3;FINDEN("&";A3&" &")-2);VERWEIS(9^9;FINDEN(" ";LINKS(A3;FINDEN("&";A3&"&") -2);ZEILE(V$1:V$99)))+1;9^9);A3)
... FINDEN("&";A3&" &")...
&" &" statt &"&"
WF
Herzlichen Dank für eure Hilfe ;)
28.12.2016 23:36:40
Christoph
Hallo zusammen,
habe mittlerweile die Tabelle, wie ich es mir vorgestellt hatte, zusammengestellt und alles funktioniert einwandfrei. Von daher Hajo_Zi, neopa_C & WF danke für eure Hilfe :)
Gruß Christoph
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige