Spalte nach Teilstring durchsuchen und Ergebnisse in einem neuen Arbeitsblatt auflist

[Rumo0815]

Hallo zusammen,
ich stehe vor einem Problem bei dem ich nicht mehr weiter weiss:

Ich habe eine umfangreiche Liste von verschiedenen Farben mit unterschiedlichen Farbbezeichnungen, die in Spalte C stehen. Insgesamt umfasst die Liste ca. 2.000 Einträge:

Pantone 7687 C
Pantone 9441 C
Pantone COOL GRAY 1 C
Pantone COOL GRAY 10 C+U
Pantone COOL GRAY 11 C+U
Pantone COOL GRAY 5 C
Pantone COOL GRAY 6 C
Pantone COOL GRAY 8 C
Pantone COOL GRAY 9 C
PANTONE® PURPLE C
HKS 13 K
Pantone BLACK 6 C+U
Pantone COOL GRAY 1 C
Pantone COOL GRAY 10 C

Aus dieser Liste möchte ich die verschiedenen Farben per Formel herausfiltern. Im o.g. Beispiel alle Farben, die die Bezeichnung HKS enthalten. Hierzu benutze ich die folgende Formel:

{=WENN(ZÄHLENWENN(Tabelle1!$C$6:$C$3000;A1&"*")

in A1 steht der Begriff "HKS"

Als Ergebnis erhalte ich #NV

Kann mir hier jemand helfen?
Vielen Dank im Voraus und für die Mühe!

Gruß
Rumo0815

[steve1da]

Hola,

Code:

=WENNFEHLER(INDEX(Tabelle1!$C$6:$C$3000;AGGREGAT(15;6;ZEILE($A$6:$A$3000)-5/(ISTZAHL(SUCHEN($A$1;Tabelle1!$C$6:$C$3000)));ZEILE(A1)));"")

Gruß,
steve1da

[Rumo0815]

Hallo steve1da,
das ging ja superschnell mit der Antwort.
Die Formel hat soweit funktioniert und isoliert den in der Beispielliste aufgeführten Eintrag.
Jetzt ist es aber so, dass wenn ich die Formel im neuen Tabellenblatt herunterkopiere, auch die danach folgenden Einträge, die nicht "HKS" enthalten aufgelistet werden. Ich wollte nur die Einträge erhalten, in denen HKS vorkommt.

Beispiel in Tabelle1, Spalte C:

Pantone 171 C
Pantone 179 C
HKS 61 K
Pantone 3435 C+U
Pantone 157 C
Pantone 158 C
Pantone 159 C
Pantone COOL GRAY 6 C
Pantone COOL GRAY 8 C
Pantone COOL GRAY 9 C
PANTONE® PURPLE C
HKS 13 K
Pantone BLACK 6 C+U
Pantone COOL GRAY 1 C
Pantone COOL GRAY 10 C

ergibt als neue Liste in Tabelle2untereinander:

HKS 61 K
HKS 13 K

Kannst Du die Formel dahingehend modifizieren?

Viele Grüße

[steve1da]

Hola,

bei mir kommt genau dein Wunschergebnis raus.

Gruß,
steve1da

[Rumo0815]

Hallo steve1da

ich habe das Beispiel nochmals in einer neuen Excel-Tabelle nachvollzogen.
Ich benutze Excel aus Office 14
Ich habe Deine Formel sowohl so wie zuvor angegeben genutzt
Ich habe die Formel auch als Matrixformel mit {} per Tastenkombination abgeschlossen.
In beiden Fällen erhalte ich lediglich den Eintrag "HKS 13" als Ergebnis, "HKS 61" findet er nicht.
In beiden Fällen tauchen keine Fehlermeldungen in den nach unten kopierten Zellen auf.

Woran könnte das jetzt liegen, dass mein Ergebnis von Deinem abweicht?

[steve1da]

Hola,

zeige bitte diese neue Datei mit meiner Formel.

Gruß,
steve1da

[Rumo0815]

  SpalteDurchsuchen.xlsx (Größe: 11,62 KB / Downloads: 3)
Hallo steve1da,
als Dateianhang das Beispiel

[steve1da]

Hola,

auf einmal fangen die Daten ja auch in C2 an!

Code:

=WENNFEHLER(INDEX(Tabelle1!$C$2:$C$3000;AGGREGAT(15;6;ZEILE($A$2:$A$3000)-1/(ISTZAHL(SUCHEN($A$1;Tabelle1!$C$2:$C$3000)));ZEILE(A1)));"")

Gruß,
steve1da

[Rumo0815]

Hallo steve1da,
jetzt klappt das!
Vielen vielen Dank für Deine Hilfe und die Zeit, die Du für mich "verbraten" hast!