In Excel-Liste nach Wert suchen - Ergebnis in einer(!) Zelle ausgebe?

Hallo,

ich benötige Hilfe bei enem Problem, dass ich auch nach intensiver Recherche bisher nicht lösen konnte. Es geht dabei um folgendes:
Excel soll eine Liste nach einem Wert durchsuchen (z.B. „ED“) und bei einem Treffer den Wert (in meinem Fall ist es Text) der benachbarten Zelle ausgeben. Hierzu verwende ich die Formel „=INDEX($A$2:blush:B$20;VERGLEICH(„ED“;$A$2:blush:A$20;0);2)“. Hierbei bricht Excel allerdings die Suche nach dem ersten Treffer ab.
Meine Frage ist nun: Wie muss die Formel lauten, damit Excel die Suche nach einem ersten Treffer bis zum Ende weiterführt und bei weiteren Treffern jeweils den Wert (Text) der benachbarten Zelle ausgibt? Die Ausgabe des Ergebnisses soll in einer(!) Zelle geschehen und mit Komma getrennt sein.

Hallo,

vor einer Antwort noch eine Rückfrage: Ich könnte wahrscheinlich eine einfache Lösung mit einer Hilfsspalte anbieten, die allerdings nur sinnvoll ist, wenn der zu durchsuchende Bereich wie bei dir angegeben relative klein ist, so ab 50 Zeilen würde ich nach anderen Lösungen suchen.
Gruß Michael

Hallo Michael,
vielen Dank für deine Rückmeldung. Im Moment ist die Liste noch nicht allzu lang, allerdings ist Sie variabel, d.h. sie könnte u.U. auch länger werden. Das spricht dann wohl eher für die andere Lösung, obwohl ist als Excel-Anfänger auch an der einfachen Version interessiert wäre, allein schon aus Lernzwecken. Aber ich möchte auf keinen Fall mehr Umstände machen als nötig.

Gruß
Billy

Hi Billy,
leider ist es in diesem Forum schwierig, sinnvoll einen Ausschnitt einer Excel Tabelle einzupflegen. Darum eine etwas umständliche Schilderung:

Spalte A = Deine Suchbegriffe, z.B. „ABC“ und „ED“
Spalte B = Text, der ausgegeben werden soll (z.B Wert1, Wert2, etc)
Spalte C = Hilfsspalte, in der nur die gesuchten Textzellen aus Spalte B übernommen werden.
Lass die ersten beiden Zeile in Spalte A und B frei und fülle diese Spalten dann ab Zeile drei.
In C1 gibst du deinen Suchbegriff ein, also „ED“
In C3 gibts du folgende Formel ein: =WENN(A3=$C$1;B3;"") und kopierst sie bis ans Ende der Spalte.
Nun sollte Spalte C nur die richtigen Textzellen ausgeben.
In C2 nun die folgende (je nach Zeilenzahl ziemlich lange) Formel einsetzen, hier bis Zeile 41

=WECHSELN(GLÄTTEN(C3&" „&C4&“ „&C5&“ „&C6&“ „&C7&“ „&C8&“ „&C9&“ „&C10&“ „&C11&“ „&C12&“ „&C13&“ „&C14&“ „&C15&“ „&C16&“ „&C17&“ „&C18&“ „&C19&“ „&C20&“ „&C21&“ „&C22&“ „&C23&“ „&C24&“ „&C25&“ „&C26&“ „&C27&“ „&C28&“ „&C29&“ „&C30&“ „&C31&“ „&C32&“ „&C33&“ „&C34&“ „&C35&“ „&C36&“ „&C37&“ „&C38&“ „&C39&“ „&C40&“ „&C41);“ „;“, ")

Damit werden alle Treffer in einer Zelle mit Komma getrennt dargestellt. Sollten deine Textstellen aber Leerzeichen enthalten, dann kommt die Formaterung schon durcheinander.Ansonsten ist die Formel ist beliebig verlängerbar und funktioniert – aber wahrscheinlich gibt es auch sehr viel elegantere Lösungen. Wenn das ganze auf Grund der unübersichtlichen Darstellung nicht nachzuvollziehen ist kann ich Dir die Tabelle auch mailen - müßtest mir eine eMailadresse schicken.

Gruß Michael

Hallo,

wenn Du mal ne anonymisierte Bsp.-Datei posten würdest, wäre dies gut.
Und als Anfänger wäre sicher auch ne Pivottabelle geigneter, als eine Matrixformel!:wink:

16BIT

Darfs auch etwas VBA-Code sein?

In diesem Beispiel wird nach dem Text in Zelle A1 gesucht und wenn er gefunden wird, die jeweils 2.Spalte mit Komma getrennt in Zelle A2 ausgegeben.

Ich denke das ist ein kleiner Lösungsansatz - wenn auch die Aufgabe wahrscheinlich etwas komplexer ist.

Sub Loesung()

Dim iZahl As Integer
Dim strLoesung As String

For iZahl = 2 To Tabelle1.UsedRange.Rows.Count
If Tabelle1.Cells(iZahl, 1) = _
Tabelle1.Cells(1, 1) Then
strLoesung = strLoesung & " " & Tabelle1.Cells(iZahl, 2) & „,“
End If
Next iZahl

Tabelle1.Cells(1, 2) = strLoesung

End Sub

Gruß Excel-Fan

Hallo Michael,
vielen Dank für deinen Lösungsansatz. Ich konnte deiner Schilderung problemlos folgen. Allerdings, dass hattest du auch bereits erwähnt, gibt es Probleme bei der Darstellung wenn der Text Leerstellen enthält. Und das ist in meinem Fall leider so. Evtl. könnte man anstelle des Kommas mit einem Zeilenumbruch arbeiten, aber das habe ich noch nicht zufriedenstellend hinbekommen. Zudem, und das ist ein weiteres Problem, wird es umständlich, wenn man nach mehreren Werten gleichzeitig sucht. Aktuell müsste ich für jeden weiteren Wert eine weitere Hilfsspalte einfügen. Gibt es doch noch eine andere Möglichkeit?

Gruß
Billy

Sub Loesung()
Dim iZahl As Integer
Dim strLoesung As String
For iZahl = 2 To Tabelle1.UsedRange.Rows.Count
If Tabelle1.Cells(iZahl, 1) = _
Tabelle1.Cells(1, 1) Then
strLoesung = strLoesung & " " & Tabelle1.Cells(iZahl,
2) & „,“
End If
Next iZahl
Tabelle1.Cells(1, 2) = strLoesung
End Sub

Hallo Excel-Fan

Eine tolle Lösung! Ich habe zu Deinem Makro Vorschläge für eine Aenderung und für eine „Schönheitsoperation“:

For iZahl = 2 To Tabelle1.UsedRange.Rows.Count + 1
Ohne das „+1“ verarbeitet das Makro ein Vorkommen des gesuchten Begriffs in der letzten Zelle der Spalte A nicht.

Dann würde ich nach „Next iZahl“ noch folgendes einfügen:
strLoesung = Trim(Left(strLoesung, Len(strLoesung) - 1))
Damit entfällt der Leerschlag vor dem ersten Buchstaben des Resultates und des Kommas danach.

Weiter: Billy erwähnt in einem seiner Beiträge: „Probleme bei der Darstellung, wenn der Text Leerstellen enthält“ - gemeint ist wohl, wenn in Spalte B leere Zellen vorkommen. Deshalb habe ich Deine If-Bedingung so ergänzt:

If Tabelle1.Cells(iZahl, 1) = _
Tabelle1.Cells(1, 1) And Not IsEmpty(Tabelle1.Cells(iZahl, 2)) Then

Viele Grüsse Niclaus

1 Like

Hallo Niclaus,
nach den Änderungen funktioniert das Makro noch besser, vielen Dank.
Optimal für meine Aufgabe wäre eine Lösung die folgendes kann:
Ich suche nach mehreren verschiedenen Werten und das Ergebnis für die gesuchten Werte sollen separat (in unterschiedlichen Zellen) angezeigt werden. Dabei sollen die zu suchenden Werte nicht manuell eingegeben werden sondern schon im Makro selbst enthalten sein. Könntest du das ggf. an einen Beispiel für sagen wir 3 verschiedene Werte aufzeigen?
Das wäre mir eine große Hilfe.

Gruß
Billy

Hallo Billy

nach den Änderungen funktioniert das Makro noch besser,

Das freut mich.

Optimal für meine Aufgabe wäre eine Lösung die folgendes kann:
Ich suche nach mehreren verschiedenen Werten und das Ergebnis
für die gesuchten Werte sollen separat (in unterschiedlichen
Zellen) angezeigt werden. Dabei sollen die zu suchenden Werte
nicht manuell eingegeben werden sondern schon im Makro selbst
enthalten sein. Könntest du das ggf. an einen Beispiel für
sagen wir 3 verschiedene Werte aufzeigen?

Das lässt sich gut machen. Ich habe aber noch Fragen dazu:

a) Bezieht sich alles immer noch auf die Spalten A:B (gemäss Makro von Excel-Fan) bzw. auf den Bereich A2:B20 gemäss Deinen ursprünglichen Angaben?

b) Sag doch bitte, in welchen Zellen die drei Resultate ausgegeben werden soll. Das macht die Arbeit etwas einfacher.

Viele Grüsse Niclaus

Hallo Niclaus,

danke das du so schnell geantwortet hast.

Ich hatte die Einschränkung auf z.B. nur 3 Werte und den kleinen zu durchsuchenden Bereich gemacht um nicht allzu viel Aufwand zu provozieren. An welcher Stelle die Ergebnisse ausgegeben werden ist im Grunde egal.
Ich nahm an, bzw. nehme noch immer an, dass das im Nachgang noch leicht von mir zu ändern bzw. erweiterbar wäre. Das trifft auch auf den zu durchsuchenden Bereich und die Anzahl der zu suchenden Werte zu.
Aber wahrscheinlich bin ich als absoluter VBA-Novize mal wieder zu naiv.

Gruß
Billy

Hallo Billy

Ich hatte die Einschränkung auf z.B. nur 3 Werte und den
kleinen zu durchsuchenden Bereich gemacht um nicht allzu viel
Aufwand zu provozieren.

Am Aufwand ändert sich kaum etwas. Es geht jetzt also um die Spalten A:B insgesamt.
Weiter habe ich im Makro drei Suchbegriffe definiert: ED, KL, PQ

An welcher Stelle die Ergebnisse ausgegeben werden ist im Grunde egal.

In meinem Makro habe ich vorgesehen, dass die Ausgabe in den Zellen D2:smiley:xx erfolgt, für jeden Suchbegriff eine entsprechende Zelle.

Ich nahm an, bzw. nehme noch immer an, dass das im Nachgang
noch leicht von mir zu ändern bzw. erweiterbar wäre.

Selbstverständlich!

Aber wahrscheinlich bin ich als absoluter VBA-Novize mal wieder zu naiv.

Wenn Du den Begriff Novize erwähnst, kennst Du Dich vielleicht auch aus im katholischen Ordensrecht: Du wirst die zeitlichen „VBA-Professen“ problemlos ablegen können. Für die feierliche oder ewige Profess wird es wohl nur den wenigsten von uns Laien-VBA-Anwendern je reichen: Man hat da nie ausgelernt!

Hier nun Variante 2 des Makros. Wenn Du Fragen dazu hast, dann weisst Du ja, wo Du eine Antwort erhalten kannst. Viel Erfolg und viele Grüsse Niclaus

Sub LoesungV2()

Dim iZahl, yZahl, z As Integer
Dim strLoesung As String

Dim strSuche(5) As String: yZahl = 5
' Wenn mehr als 5 Suchbegriffe nötig wären,
' müssten hier die beiden Zahlen entsprechend erhöht werden.

strSuche(1) = "ED"
strSuche(2) = "KL"
strSuche(3) = "PQ"
' Die Suchbegriffe müssen/sollten lückenlos durchnummeriert werden.

Tabelle1.Range(Cells(2, 4), Cells(2 + yZahl - 1, 4)).ClearContents

For z = 1 To yZahl
 strLoesung = ""
 If strSuche(z) = "" Then GoTo finis1
 For iZahl = 2 To Tabelle1.UsedRange.Rows.Count + 1
 If Tabelle1.Cells(iZahl, 1) = strSuche(z) \_
 And Not IsEmpty(Tabelle1.Cells(iZahl, 2)) Then
 strLoesung = strLoesung & " " & Tabelle1.Cells(iZahl, 2) & ","
 End If
 Next iZahl
finis1:
 If Len(strLoesung) = 0 Then strLoesung = "- "
 strLoesung = Trim(Left(strLoesung, Len(strLoesung) - 1))
 Tabelle1.Cells(z + 1, 4) = strLoesung
Next z

End Sub

Hallo Niclaus,

zum Einen, danke das du mir Hoffnung machst :wink:
…und zum Anderen, dein Makro funktioniert genau so wie von dir beschrieben.
Ich kann es auch problemlos erweitern. Super, dafür schon mal vielen Dank.
Wenn du erlaubst, möchte ich noch ein, zwei weitere Fragen an dich richten:
Wie kann die Ausgabe so gesteuert werden, dass das Ergebnis für den Wert „ED“ in Zelle C2, für „KL“ in Zelle D4 und PQ in E2 erscheint?
Wenn sich meine Liste (in A:B) ändert muss das Makro erneut (manuell) ausgeführt werden damit sich die Ergebnisse aktualisieren. Das könnte ich mit einem Button realisieren den ich anklicke um alles zu aktualisieren. Gibt es u.U. noch eine andere Möglichkeit? Besser wäre es, wenn sich das Ergebnis unmittelbar nach der Eingabe ändern würde.

Gruß
Billy

Hallo Billy

zum Einen, danke das du mir Hoffnung machst :wink:

Einen wichtigen Grundsatz Deines VBA-Noviziates hast Du ja schon intus: Bei einem solchen Projekt Schritt für Schritt vorgehen. Gespannt bin ich, wie die nächsten Schritte sind!

Wie kann die Ausgabe so gesteuert werden, dass das Ergebnis
für den Wert „ED“ in Zelle C2, für „KL“ in Zelle D4 und PQ in
E2 erscheint?

„PQ“ wirklich in E2? - In meiner Tabelle ist dann der Inhalt von C2 oft nicht mehr ganz sichtbar! - Aber Dein Wunsch ist mir Befehl!
Siehe unten Version 3 des Makros

Wenn sich meine Liste (in A:B) ändert muss das Makro erneut
(manuell) ausgeführt :andere Möglichkeit? Besser wäre es, wenn sich das Ergebnis
unmittelbar nach der Eingabe ändern würde.

Auch das geht. Aber bevor ich mich dahinter mache, brauche ich dringend einen Kaffee und eine Zigarette. Und dann schau ich, wie der Feierabendverkehr ist, und mache mich allmählich auf den Weg nach Hause. - Du wirst von mir hören. Bis dahin Grüsse Niclaus

Sub LoesungV3()

Dim iZahl, yZahl, z As Integer
Dim strLoesung As String

Dim strSuche(3) As String
Dim strZiel(3) As String
yZahl = 3
' Wenn mehr als 3 Suchbegriffe nötig wären, müssten
' hier die drei obigen Zahlen entsprechend erhöht werden.

strSuche(1) = "ED": strZiel(1) = "C2"
strSuche(2) = "KL": strZiel(2) = "D4"
strSuche(3) = "PQ": strZiel(3) = "E2"
' Die Suchbegriffe und die Ziele müssen/sollten lückenlos durchnummeriert werden.

For z = 1 To yZahl
 Tabelle1.Range(strZiel(z)).ClearContents
 strLoesung = ""
 If strSuche(z) = "" Then GoTo finis1
 For iZahl = 2 To Tabelle1.UsedRange.Rows.Count + 1
 If Tabelle1.Cells(iZahl, 1) = strSuche(z) \_
 And Not IsEmpty(Tabelle1.Cells(iZahl, 2)) Then
 strLoesung = strLoesung & Tabelle1.Cells(iZahl, 2) & ", "
 End If
 Next iZahl
finis1:
 If Len(strLoesung) = 0 Then strLoesung = "- "
 strLoesung = Left(strLoesung, Len(strLoesung) - 2)
 Tabelle1.Range(strZiel(z)) = strLoesung
Next z

End Sub

Hallo Billy

Wenn sich meine Liste (in A:B) ändert muss das Makro erneut
(manuell) ausgeführt werden damit sich die Ergebnisse
aktualisieren. Das könnte ich mit einem Button realisieren den
ich anklicke um alles zu aktualisieren. Gibt es u.U. noch eine
andere Möglichkeit? Besser wäre es, wenn sich das Ergebnis
unmittelbar nach der Eingabe ändern würde.

Bei einer Aenderung in Spalte A oder in Spalte B soll das Makro LoesungV3() ausgeführt werden - siehe meinen Beitrag von 17:24 Uhr. - Mit folgendem Makro geht das:

Private Sub Worksheet\_Change(ByVal Target As Range)
 If Target.Column 

ABER ACHTUNG: Bisher hast Du die Makros im VBA-Projekt unter den Modulen (in Modul1 oder Modul2 usw.) eingegeben. Dieses neue Makro musst Du unter Microsoft Excel Objekte in Tabelle1 eingeben. - Dort also "Tabelle1" doppelklicken und dann im wahrscheinlich leeren Bereich die drei Zeilen eingeben. 

Ich hoffe, ich habe mich unklar genug ausgedrückt und es klappt trotzdem.

Viele Grüsse Niclaus

Hallo Niclaus,
funktioniert alles wunderbar, vielen Dank.
Da meine Aufgabe einigermaßen komplex ist, werde ich bestimmt noch ein paar Mal Hilfe benötigen, insbesondere weil ich - da ich nun gesehen habe was alles möglich ist - fest entschlossen bin, mir VBA für Excel anzueignen.
Als Autodidakt wird das sicherlich kein leichtes Unterfangen, daher bin ich auch hier für jeden Tipp oder für jede Empfehlung zu Büchern, zu Foren oder anderen hilfreichen Internetseiten etc. sehr dankbar.

Viele Grüße
Billy