Eine Spalte zu Zeile mit weiteren Bedinungen

[NoGuy]

Guten Tag Zusammen,

mein Anliegen ist "etwas komplizierter" und hoffe mal ich habe die Beschriftung richtig getroffen :D

In der Tabelle steht in Spalte A die Vertragsnummer. In Spalte B und C der Versicherungsnehmer. In Spalte D finden sich der Versicherungsnehmer erneut mit Vornamen und darunter weitere Versicherte Personen. Nun Kann unserer System nur eine Zeile einlesen. Das bedeutet würde wir das wie oben in der Tabelle einlesen erstellt unser System automatisch beim Kunden Mustermann 5 Mails. Das wollen wir natürlich nicht. Die Versicherten Personen sollen in der obersten Zeile der gleichen Vertragsnummer in separaten Spalten stehen und am Besten die anderen löschen oder so sichtbar machen das sie leicht zu löschen sind. Die Liste hat 2300 Posten ^^


  Test 2.xlsx (Größe: 8,42 KB / Downloads: 5)

Was habe ich versucht:

Die Tabelle zu drehen löst leider nicht das Problem das alles in einer Zeile  stehen muss.

Mit Wenn Funktionen habe ich es geschafft, doch es ist sehr unüberschaubar und falls ich es nicht händisch löschen müsste spare ich mir ein Haufen Arbeit :D

Grüße und noch einen schönen Tag und Danke im Voraus

No Guy

[steve1da]

Hola,

so?
Wichtig: die Formel in A3 nochmal mit F2 bearbeiten und mit Strg-Shift-Enter abschließen.
Gruß,
steve1da

[NoGuy]

Vielen lieben Dank Steve1da!

Hab die Werte auf meine Tabellen angepasst und es funktioniert wunderbar. 

Jetzt gibt es nur noch ein kleines Problem. 

Anstatt wie in der Tabelle

[
Bild bitte so als Datei hochladen: Klick mich!
]

Kann auch folgendes vorkommen

[
Bild bitte so als Datei hochladen: Klick mich!
]


  Beispiel 1.xlsx (Größe: 8,14 KB / Downloads: 6)
der Name der Versicherungsnehmer steht manchmal nicht direkt in der obersten Zeile. Somit wird mir Peter gelöscht und zweimal Max geschrieben. 
Er kann an 2, 3 oder 4.. stelle stehen. Ich habe die Excel nach Nummer Namen Versicherte Person sortiert, dennoch sind die Versicherten Personen nicht immer in alphabetischer Reihenfolge.

Es können ruhig alle Versicherten Personen aufgeführt werden auch wenn der Name doppelt vorkommt. Besser wäre es wenn natürlich nicht oder in einer bestimmten Spalte. 

Hoffe du kannst mir dabei weiterhelfen :)

LG NoGuy

[Cadmus]

Hallo,
anbei mal eine Möglichkeit mit PowerQuery (in 2016 integriert).

Aufbauend auf https://exceloffthegrid.com/power-query-...ngle-cell/

[Fred11]

Hallo NoGuy,

mit ein paar kleinen Anpassungen / Ergänzungen der Formel in den Spalte D:I  lässt sich das realisieren.
(basierend auf der Beispieldatei und den Formeln von steve1da aus #2)

Ausgangstabelle:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Vertragsnummer	Name	Vorname	Versicherte Personen
2	00 123 456 A	Mustermann	Max	Peter
3	00 123 456 A	Mustermann	Max	Max
4	00 123 456 A	Mustermann	Max	Sabine
5	00 123 456 A	Mustermann	Max	Günther
6	00 123 456 A	Mustermann	Max	Rudi
7	00 124 789 A	Musterfrau	Susanne	Susanne
8	00 124 789 A	Musterfrau	Susanne	Gisela
9	00 456 852 U	Musterstein	Karsten	Karsten
10	00 456 852 U	Musterstein	Karsten	Dieter
11	00 789 852 U	Musterknabe	Emil	Emil

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

auflisten aller versicherten Personen:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F	G	H	I
1	Vertragsnummer	Name	Vorname	Versicherte Personen
2	00 123 456 A	Mustermann	Max	Peter	Max	Sabine	Günther	Rudi
3	00 124 789 A	Musterfrau	Susanne	Susanne	Gisela
4	00 456 852 U	Musterstein	Karsten	Karsten	Dieter
5	00 789 852 U	Musterknabe	Emil	Emil

Zelle	Formel
D2	=WENNFEHLER(INDEX(Tabelle1!$D$2:$D$11;AGGREGAT(15;6;ZEILE(Tabelle1!$D$2:$D$11)-1/(Tabelle1!$A$2:$A$11=$A2);SPALTE(A1)));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

auflisten der mitversicherten Personen:

Arbeitsblatt mit dem Namen 'Tabelle2 (2)'
	A	B	C	D	E	F	G	H	I
1	Vertragsnummer	Name	Vorname	mitversicherte Personen
2	00 123 456 A	Mustermann	Max	Peter	Sabine	Günther	Rudi
3	00 124 789 A	Musterfrau	Susanne	Gisela
4	00 456 852 U	Musterstein	Karsten	Dieter
5	00 789 852 U	Musterknabe	Emil

Zelle	Formel
D2	=WENNFEHLER(INDEX(Tabelle1!$D$2:$D$11;AGGREGAT(15;6;ZEILE(Tabelle1!$D$2:$D$11)-1/((Tabelle1!$A$2:$A$11=$A2)*(Tabelle1!$D$2:$D$11<>C2));SPALTE(A1)));"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß
Fred

[NoGuy]

Vielen Dank Fred :) 

Ich muss leider sagen das es nach der zweiten Methode nur teilweise funktioniert.
Bei manchen Posten übernimmt er das richtig und nimmt die Versicherte Person die zugleich auch Vorname ist nicht mit. Hier ist es auch egal an welcher Stelle sie stehen. 

Bei anderen wiederum nimmt er nur den zweiten Posten der Versicherten Personen nicht. Es kann somit auch sein das ein anderer Name gelöscht wird.

Dachte mit deiner Formel, wird die Zelle der Versicherten Personen mit der Zelle davor Vorname verglichen und falls der Name vorkommt, wird dieser nicht genutzt.
Ist das richtig?

[Fred11]

Hallo No Guy,

Dachte mit deiner Formel, wird die Zelle der Versicherten Personen mit der Zelle davor Vorname verglichen und falls der Name vorkommt, wird dieser nicht genutzt.
Ist das richtig?

Ja, das ist richtig.

Das macht (eigentlich) der Formelteil (der Formel in D2)
*(Tabelle1!$D$2:$D$11<>C2)

Aber in diesem Formelteil steckt tatsächlich noch ein kleiner Fehler drin; da fehlt ein Dollarzeichen vor C2, damit sich die Formel beim nach rechts ziehen immer auf die Spalte C bezieht.
So ist es richtig:
*(Tabelle1!$D$2:$D$11<>$C2))

siehe angehängte Beispieldatei

Gruß
Fred