Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1732to1736
1732to1736
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suchfunktion für mehrer Textergebnisse

Forumthread: Suchfunktion für mehrer Textergebnisse

Suchfunktion für mehrer Textergebnisse
10.01.2020 07:04:39
Hans
Hallo zusammen,
ich habe wenig Excelkenntnisse und suche eine Formel ähnlich Summenprodukt oder Summewenns, die mir mehrere gefundene Argumente ("Text" Argumente") in der gleiche Zelle mit Semikolon wiedergibt.
Anbei ein Beispiel
https://www.herber.de/bbs/user/134292.xlsx
Danke vorab für jede Hilfe
Grüße
Hans
Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Hans in E5 ? Sollte da nicht Helmut hin? owT
10.01.2020 07:29:28
Matthias
Auflistung in einer Zelle
10.01.2020 08:10:33
WF
Hi,
in E3 folgende Arrayformel:
{=WENN(D3="";"";INDEX(B:B;KKLEINSTE(WENN(A$1:A$99=D3;ZEILE(X$1:X$99));1))&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$99=D3;ZEILE(X$1:X$99));2));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$99=D3;ZEILE(X$1:X$99));3));"")) }}
runterkopieren
Ich hab mich jetzt auf maximal 3 Treffer beschränkt.
WF
Eingabe Arrayformel:
Du kopierst Dir die Formel.
Dann gehst Du in die Bearbeitungszeile, löschst die {geschweiften Klammern} am Anfang und Ende und schließt ab mit GLEICHZEITIG: Strg Shift Enter (statt Enter allein). Dadurch werden diese Klammern erzeugt.
Anzeige
AW: Auflistung in einer Zelle
10.01.2020 08:40:15
Hans
Danke @ WF.
Ich bräuchte eine Formel, die bis zu 10 Treffer ausgibt. Wie würde hier die Formel ausschauen?
Danke!
AW: Auflistung in einer Zelle
10.01.2020 08:42:07
Hans
Danke @ WF.
Ich bräuchte eine Formel, die bis zu 10 Treffer ausgibt. Wie würde hier die Formel ausschauen?
Danke!
das zusammenzukopieren ist null Aufwand
10.01.2020 09:46:46
WF
Hi,
für jetzt 10 Treffer:
{=WENN(D3="";"";INDEX(B:B;KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));1))&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));2));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));3));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));4));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));5));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));6));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));7));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));8));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));9));"")&WENNFEHLER("; "&INDEX(B:B; KKLEINSTE(WENN(A$1:A$100=D3;ZEILE(X$1:X$99));10));"")) }
WF
Anzeige
AW: dynamische Ergebnisliste mit 2 Formeln ...
10.01.2020 08:45:27
neopa
Hallo hans,
... und zwar mit 2 Matrixfunktion(alität)sformeln die keines spez. Formelabschluss wie eine klassische Matrixformel benötigen.
Nachfolgende Formel D3 nach unten und E3 weit genug nach rechts und unten ziehend kopieren.
Die Holfsspalten F bis Z kannst Du nachträglich ausblenden.
Auf evtl. Fragen kann ich vor Montag Abend jedoch nicht antworten, weil ich gleich offline sein werde.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGH
1   Spalte A ohne DuplikateErgebnisse ohne Duplikate (Formel gesucht mit Referenz zu Spalte D)
2NummerName NummerNamen   
3666020Tina 666020Tina; SiegfriedSiegfried  
4667030Linda 667030Linda; Hans; TinaHans; TinaTina 
5667030Hans 667040Helmut   
6666020Siegfried      
7667040Helmut      
8666020Tina      
9667030Hans      
10667030Tina      
11667030Hans      
12        

ZelleFormel
D3=WENNFEHLER(AGGREGAT(15;6;A$3:A$99/(A$3:A$99>"")/(ZÄHLENWENN(D$14:D14;A$3:A$99)=0);1);"")
E3=WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE(A$3:A$99)/($A$3:$A$99=$D3)/(VERGLEICH($A$3:$A$99&$B$3:$B$99;$A$3:$A$99&$B$3:$B$99;0)=ZEILE(B$3:$B$99)-2);SPALTE(A2)))&WENN(F3="";"";"; "&F3);"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: dynamische Ergebnisliste mit 2 Formeln ...
10.01.2020 10:06:28
Hans
Danke, super Hilfe / super Leute hier!
Einzig die Formel aus D3 funktioniert noch nicht richtig beim Herunterziehen. Liegt das vielleicht an dem Bezug " ZÄHLENWENN(D$14:D14 " ?
Danke und Grüße
ja, da ist ne Macke drin
10.01.2020 11:00:12
WF
Hi,
richtig lautet D3:
=WENNFEHLER(AGGREGAT(15;6;A$3:A$99/(A$3:A$99"")/(ZÄHLENWENN(D$2:D2;A$3:A$99)=0);1);"")
Und die Formel in E3 ist viel zu lang /zu kompliziert.
Als Arrayformel in E3:
{=WENNFEHLER(INDEX($B:$B;KKLEINSTE(WENN($A$1:$A$99=$D3;ZEILE($X$1:$X$99));SPALTE(A$1)))&WENN(F3""; "; "&F3;"");"") }
nach rechts und nach unten kopieren
WF
Anzeige
AW: bzgl. E3 hast Du nicht berücksichtigt ...
12.01.2020 20:17:01
neopa
Hallo WF,
... dass Hans vorgeben hatte: "Ergebnisse ohne Duplikate".
Daran hatte ich mich meiner Formelvorschlag mit E3 gehalten und deshalb ist meine Formel natürlich auch komplizierter. Mit Deiner Formel ist dies nicht zu realisieren.
Gruß Werner
.. , - ...
AW: die falsche Bezugsangabe in Formel D3 ...
12.01.2020 20:07:31
neopa
Hans,
... hat für das Ergebnis in D3 selbst keinen ändernden Einfluss. Die Formel funktioniert aber dann nicht mehr, wenn man sie nach unten ziehend weiter kopieren muss, wie von mir auch angegeben. Sorry.
Zur Erklärung wie es dazu kam:
Der hier im Beitrag für D3 angezeigte ungewollte Formeltext D$14:D14 entstand dadurch, dass ich kurz vor dem Kopieren mit Tab2Html, den bis dahin von mir in D2 stehenden Text (hatte dort den Link auf den thread zu stehen) noch schnell in D14 verschoben habe (also außerhalb des hier dargestellten Auszugsbereiches) und in die Zeile 2 die richtigen Textüberschriften der Vollständigkeit halber ergänzte. Die Formeln ab D4 und deren Ergebnisse blieben somit unberührt und damit korrekt. Somit war es mir leider auch nicht aufgefallen.
Die Formel D3 muss zum ziehenden herunter kopieren natürlich wie folgt korrekt lauten:
=WENNFEHLER(AGGREGAT(15;6;A$3:A$99/(A$3:A$99"")/(ZÄHLENWENN(D$2:D2;A$3:A$99)=0);1);"")
Gruß Werner
.. , - ...
Anzeige
Wen interessiert das ?
12.01.2020 20:32:21
WF
.
AW: warst denn Du der Fragesteller? owT
13.01.2020 08:23:11
neopa
Gruß Werner
.. , - ...
Textverketten ...
10.01.2020 11:12:59
Matthias
obwohl Du meine Frage ignoriert hast, hier meine Variante,
Natürlich muss immer eine Nummer und ein Name vorhanden sein.
Tabelle1

 ABCD
3666020Tina666020Tina;Siegfried;Karin;Werner;Peter;Helmut
4667030Linda667030 
5667030Hans667040 
6666020Siegfried  
7666020Karin  
8666020Werner  
9667030Walter  
10667030Marco  
11666020Peter  
12666020Helmut  

Formeln der Tabelle
ZelleFormel
D3=WENN(ZÄHLENWENN($A$3:$A$12;C3)>0;WECHSELN(WECHSELN(TEXTVERKETTEN(";";1;WENN($A$3:$A$12=$C3;$B$3:$B$12));FALSCH;";");";;";"");"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Formel aus D3 runterkopieren
Gruß
Anzeige
Textverketten.in Excel 2016 ? hab ich nicht...Gruß
10.01.2020 11:17:57
robert
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige