2 Spalten, WENN Wert aus 1 in 2 enthalten DANN schreibe in 3

[ulmi82]

Hallo ihr Lieben.

Ich verzweifle bald. Ich habe eine riesige Datenmenge und konnte mir bisher nur händisch helfen. Aber das dauert ewig. Ich beschreibe kurz.

Spalte 1 enthalt eine Kopfrufnummer und Spalte 2 enthält Kopfrufnummer plus Durchwahl.

Beispiel:

Spalte1      Spalte2
0221783    0221783555
03771553  0221783556
066189      0377155341133

Es gibt somit mehr Rufnummern pro Kopfrufnummer. Ich benötige in Spalte 3 zum weiteren verarbeiten die jeweilige Kopfrufnummer. Theoretisch braucht es wohl eine Schleife, aber VBA ist nicht mein Ding. Es muss quasi geprüft werden, ob in Spalte2 der Wert aus Spalte 1 enthalten ist und wenn ja, der Wert aus Spalte 1 in Spalte 3 geschrieben werden. Solange der Wert aus der aktuellen Zelle in 1 enthalten ist, soll das passieren. Wenn nicht, soll der nächste Wert aus Spalte 1 genommen werden.

Bisher sieht meine Formel so aus:
=WENN(ISTFEHLER(SUCHEN($A$3;B:B;1));WENN(ISTFEHLER(SUCHEN(INDIREKT("A" & $D2015);B:B;1));FALSCH;INDIREKT("A" & $D2015));$A$3)


Dabei steht in Spalte D eine Hilfsvariable, die ich händisch erhöhe um den nächsten Datensatz in A zu erreichen. Es sind aber ca 1500 Werte in A enthalten. Das dauert.


Ideen?

[shift-del]

Hallo

Du hast es beschrieben als eine Liste mit zwei Spalten. Tatsächlich sind es aber wohl zwei Listen mit jeweils einer Spalte und für die zweite Liste soll eine zweite Spalte mit einer Formel gefüllt werden.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Kopfrufnummer		Rufnummer	Kopfrufnummer
2	0221783		0221783555	0221783
3	03771553		0221783556	0221783
4	066189		0377155341133	03771553

Zelle	Formel
D2	=VERWEIS(42;1/SUCHEN($A$2:$A$4;C2);$A$2:$A$4)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

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

[ulmi82]

Wow. Ich muss total blind gewesen sein. Kannst du mir kurz erklären, was die 42 und das 1/Suchen() bedeutet?

[shift-del]

http://excelformeln.de/formeln.html?welcher=48
Abschnitt Erläuterung

http://www.herber.de/forum/archiv/1224to...ktion.html

http://www.online-excel.de/fom/fo_read.php?f=1&bzh=72076&h=72032
Erklärung zu VERWEIS(#;1/(...);...) mit #>1 ...
Von:  neopa  Am: 17.02.2013 10:22:15

Hallo Udo,

... um Deiner Bitte nachzukommen, vorab folgendes:  Ich hab schon öfters dazu geschrieben, bin aber zu faul zum suchen, deshalb lesen sich meine Erklärungen jedesmal sicherlich anders.

Entscheidend ist, dass VERWEIS() eine der wenigen Funktionen ist, die Ergebnisse auch dann ermitteln kann, wenn Teile der Parameter Fehlerwerte beinhalten. Dies wird mit dem Konstrukt VERWEIS(9;1/(...);...) genutzt.  

Einfach nachvollziehbar ist, dass im vorliegenden Formelbeispiel und dort der Teil (A$1:A#<>0)  eine MATRIX aus Wahrheitswerten liefert. Wahrheitswerte kombiniert mit einer mathematischen Operation (egal welche) liefert numerische oder eben auch Fehlerwerte . In diesem spez. BeispielFall, weil mit 1/(A$1:A#<>0) ein Quotient gebildet wird, eine MATRIX bestehend aus "Einsen" (für 1/WAHR) und den Fehlerwerten #DIV/0! (für 1/FALSCH). Das kannst Du selbst auch mit der ExcelFunktion Formelauswertung oder nachdem Du in der Eingabezeile den entsprechenden Formelteil markiert hast, mit betätigen der Taste [F9] selbst leicht visuell nachvollziehen.

Die Funktion VERWEIS(), die bekanntlich von "hinten" (von rechts bzw. von unten) auswertet, ignoriert nun einfach jegliche Fehlerwerte ohne dadurch die Reihenfolge der MATRIXwerte zu ändern. Somit muß der erste Parameter der VERWEIS()-Formel (bei mir hier eine 9) lediglich ein Wert >1 sein, um zum gewünschten Ergebnis zu gelangen. Dies ist so, weil die erste 1 aus der der TeilMATRIX (dem Parameter "Suchvektor" von VERWEIS()) von rechts, ein Wert kleiner dem Parameter "Suchkriterium"  ist und somit den "Standort" des Ergebniswertes im Parameter "Ergebnisvektor" von VERWEIS() angibt. 

Gruß Werner
.. , - ...