Office: (Office 2013) Zellenwert übertragen, wenn Teilstring vorhanden ist und neu auflisten

Folgendes Problem:

Tabelle1
Spalte1 / Spalte2 / …

Rotfuchs / 100 / …
Grünfink / 200 / …
Blaumeise / 300 / …
Hunderot / 400 / …
Brot / 150

Immer wenn der Teilstring "*rot*" in der Spalte1 vorkommt, soll der Wert dieser Zeile von Spalte2 übernommen werden und in Tabelle2/Spalte1 ohne Leerzeilen neu aufgelistet werden. Also z. B. so:
Tabelle2
Spalte1 / ...

100 / …
400 / …
150 / …

Verwende ich den ganzen Text als Wenn-Bedingung funktioniert alles prima mit:
=WENNFEHLER(INDEX('Tabelle1'!$B$1:$B$100;KKLEINSTE(WENN('Tabelle1'!$A$1:$A$100="Hunderot";ZEILE('Tabelle1'!$B$1:$B$100));ZEILE()-3));"") - und dann die Zellen entsprechend runterkopiert.
die ZEILE()-3 ist dafür, dass ich in der vierte Zeile in Tabelle2 beginne. Das ist aber sekundär.

Für Teilstrings-Verwenden finde ich Hinweise auf
(WENN(FINDEN("*rot*";A1)>0
oder ZÄHLENWENN("*rot*" etc.

Aber ich krieg das damit einfach nicht hin damit. Und eine Makrolösung ist in dem Fall nicht gut.
Würde mich über Hilfe sehr freuen.

 

Hallo Stefan,

probier doch mal:

Code:

=WENN(ZÄHLENWENN(A1;"*rot*")>0;B1;"")

 

Hallo Achim,
vielen Dank für deine Hilfe.
Klappt auch schon gut, um die richtige Zeilen auszusortieren. Aber ich bräuchte die noch neu von oben nach unten aufgereiht, ohne LEER-Zeilen.
Also aus dem Ergebnis von:
100 / ...
LEER / ...
LEER / ...
400 / ...
150 / ...

müsste noch

100 / ...
400 / ...
150 ...
werden.

Das geht vermutlich irgendwie mit KKLEINSTE(WENN ...) ... aber diese Kombi wird bei mir nie was.

 

Hallo Stefan,

ob das ohne Leerzeilen per Formel überhaupt machbar ist, kann ich nicht sagen. Da gibt es hier andere Experten.

Aber, wie oft benötigst Du diese Auswertung überhaupt? Täglich, stündlich... monatlich, oder nur dieses eine Mal?
Denn wenn Du die nicht zu oft aufs neue benötigst, könntest Du erst die Formel benutzen, danach die komplette Spalte markieren - kopieren - nur Werte einfügen, und danach diese Spalte sortieren. Dadurch "fallen" die Leerzeilen nach unten ans Ende der Liste.

Wenn das nix für Dich ist kann ich sowas nur per Makro. Aber wie gesagt, hier tummeln sich ja auch andere, die von Formeln mehr Ahnung haben als ich.
;)

 

Danke Achim für die Hilfe,
hab ich auch schon alles überlegt. Aber ich muss das ein paar mal pro Woche machen. Die Daten werden laufend ergänzt und ausgewertet.
Ich könnte jede Tabellenspalte auf ein extra Tabellenblatt übertragen und dann so die Leerzeilen wegfiltern. Aber dann bekomme ich zig Tabellenblätter...

 

Hola,

und warum nutzt du keine Hilfsspalte? In dieser verwendest du deine 2. Formel bei der du dir eine 1 ausgeben lässt, wenn rot gefunden wird.
Mit deiner ersten Formel lässt du dir die Liste der 1er ausgeben.

Gruß,
steve1da

 

Hallo steve1da,
Ja das funktioniert. Ist etwas umständlich bei 15 Spalten und ständig hinzukommenden.
Aber wenn keine einfachere Lösung kommt mache ich es so. Danke.

 

Für alle, die hier landen, weil sie das gleiche Problem haben, hier eine Zusammenfassung:
Schritt 1:
Ich bilde eine Spalte(=Hilfspalte) auf einem neuem Tabellenblatt mit der Formel
=WENN(ZÄHLENWENN('Tabelle1'!A1;"*rot*")>0;1;"") , die ich auf die weiteren Zeilen runterkopiere.
Jetzt erscheint der Wert 1 in der gleichen Tabellenzeile, bei der in der Ursprungs-Suchspalte rot vorkommt.
Schritt 2:
In einer weiteren Spalte des Tabellenblattes2 (Endergebnis) z. B. gleich daneben, kommt die Formel
=WENNFEHLER(INDEX('Tabelle1'!$B$1:$B$1000;KKLEINSTE(WENN('Tabelle2'!$A$1:$A$1000=1;ZEILE('Tabelle1'!$B$1:$B$1000));ZEILE()-3));"") -> und runterkopieren.
In meinem Beispiel in die dritte Zeile, wegen einer Überschrift, daher die -3 am Ende der Formel. (ggf. ändern)

 

Hola,

wichtig wäre auch der Hinweis, dass die Formel in Schritt2 mit Strg-Shift-Enter abgeschlossen werden muss.
Und das -3 brauchst du nicht, wenn du Zeile(A1) schreibst.

Gruß,
steve1da