Textteilsuche und Eindeutige Werte

[loner]

Hallo,

hat hier jemand einen Lösungsvorschlag für mich wie ich aus der linken Tabelle einen spezifischen Textteil (Mitarbeitername) in der Spalte B finde und dann alle Texte aus Spalte A für den Mitarbeiter ausgeben lassen kann.

Dh ich möchte wissen welcher MA in welchen Ländern tätig ist.


Danke schon vorab für eure Zeit und Vorschläge!

[Ralf A]

Moin,

das ähnelt Deiner Anfrage von hier. Und die wurde gelöst.... Schau einfach mal da nach.

[loner]

Hallo, ja danke ich habs damit versucht, stoße aber nun durch die Daten in nur 1 Spalte an meine Grenze. Ich könnte aber die Mitarbeiter in eigene Spalten dahinter ausgeben lassen und dann mit dem von damals holen. Ist zwar nicht das was ich wollte, aber umgehen könnte man es so.

LG

[EA1950]

Hallo,

wenn deine Daten samt Kopfzeile im Bereich A2:B9 enthalten sind, müsste folgende spillende Formel funktionieren:

PHP-Code:

=LET(
    M; $A$2:.$B$99;
    entpiv; REDUCE(
        SPALTENWAHL(ZEILENWAHL(M; 1); 2; 1);
        SEQUENZ(ZEILEN(M) - 1; 1; 2);
        LAMBDA(Akk; i;
            LET(
                tma; INDEX(M; i; 2);
                lber; INDEX(M; i; 1);
                lma; TEXTTEILEN(tma; ; ", ");
                VSTAPELN(Akk; ERWEITERN(lma; ; 2; lber))
            )
        )
    );
    GRUPPIERENNACH(INDEX(entpiv; ; 1); INDEX(entpiv; ; 2); MATRIXZUTEXT; 3; 0)
)


Dabei kann der Datenbereich bis zu A2:B99 erweitert werden, ohne dass an der Formel etwas geändert werden muss.

In der beiliegenden Datei habe ich dir obige Formel in Zelle D2 eingefügt.

[_MCO_]

-	A	B	C	D	E
2	Bereich	Mitarbeiter		Mitarbeiter	Ergebnisbereich = Länder der MA
3	Land 1	A, B, C		A	Land 1, Land 4, Land 6
4	Land 2	B		B	Land 1, Land 2, Land 5, Land 7
5	Land 3	C		C	Land 1, Land 3, Land 4
6	Land 4	A, C
7	Land 5	B
8	Land 6	A
9	Land 7	B

-	A	B	C	D	E
2	Bereich	Mitarbeiter		Mitarbeiter	Ergebnisbereich = Länder der MA
3	Land 1	A, B, C		=SORTIEREN(EINDEUTIG(TEXTTEILEN(TEXTVERKETTEN(",";1;WECHSELN(B3:.B14;" ";""));;",")))	=TEXTVERKETTEN(", ";1;SORTIEREN(EINDEUTIG(ZUSPALTE(WENN(WENNFEHLER(SUCHEN(D3;$B$3:$B$14);0);$A$3:$A$14&"";"")))))
4	Land 2	B			=TEXTVERKETTEN(", ";1;SORTIEREN(EINDEUTIG(ZUSPALTE(WENN(WENNFEHLER(SUCHEN(D4;$B$3:$B$14);0);$A$3:$A$14&"";"")))))
5	Land 3	C			=TEXTVERKETTEN(", ";1;SORTIEREN(EINDEUTIG(ZUSPALTE(WENN(WENNFEHLER(SUCHEN(D5;$B$3:$B$14);0);$A$3:$A$14&"";"")))))
6	Land 4	A, C
7	Land 5	B
8	Land 6	A
9	Land 7	B

[slowboarder]

wenns etwas einfacher sein darf:

diese Formel in D3 ermittelt dir die Liste der Mitarbeiter
=SORTIEREN(EINDEUTIG(TEXTTEILEN(TEXTVERKETTEN(", ";1;B3.:.B99);;", ")))

diese Formel zeigt dir dann die Länder dazu:
=TEXTVERKETTEN("; ";1;FILTER(A3.:.A99;ISTZAHL(FINDEN(", "&D3&", ";", "&B3.:.B99&", "))))

die zweite Formel spillt nicht nach unten, dh du du musst sie von Hand nach unten ziehen und bei sich ändernden Mitarbeiten anpassen, aber dafür ist sie deutlich einfacher.

Gruß Daniel

[loner]

Ich bin wiedermal beeindruckt von eurem Wissen!

Beide Versionen von slowboarder & _MCO_ klappen toll!

Danke für euren Input!

Lg

[Jockel]

Danke schon vorab für eure Zeit und Vorschläge!

Servus, wenn's miit Power Query sein darf, sähe so mein Vorschlag aus..:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
1	Mitarbeiter	Power Query (keine Formel)
2	A	Land 1, Land 4, Land 6
3	B	Land 1, Land 2, Land 5, Land 7
4	C	Land 1, Land 3, Land 4

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 365

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

hier der M-code - keine Programier-Kenntnisse nötig..:
l

PHP-Code:

et
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1_2"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Bereich", type text}, {"Mitarbeiter", type text}, {"Spalte1", type text}}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Geänderter Typ",{"Mitarbeiter"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Spalte1", "Mitarbeiter"}}),
    #"Gruppierte Zeilen" = Table.Group(#"Umbenannte Spalten", {"Mitarbeiter"}, {{"Anzahl", each _, type table [Bereich=nullable text, Mitarbeiter=nullable text]}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Gruppierte Zeilen", "Benutzerdefiniert", each [Anzahl][Bereich]),
    #"Extrahierte Werte" = Table.TransformColumns(#"Hinzugefügte benutzerdefinierte Spalte", {"Benutzerdefiniert", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Umbenannte Spalten1" = Table.RenameColumns(#"Extrahierte Werte",{{"Benutzerdefiniert", "Power Query (keine Formel)"}})
in
    #"Umbenannte Spalten1"


[slowboarder]

hier der M-code - keine Programier-Kenntnisse nötig..:

wow, wie erstellst du sowas ohne Programmierkenntnisse?
das sieht komplexer aus als VBA-Code.

[Jockel]

wow, wie erstellst du sowas ohne Programmierkenntnisse?
das sieht komplexer aus als VBA-Code.

Hallo, das ist ein M-Code und wird durch Klicks in Power Query genriert. Ich stelle bei Interesse gern eine Beispiel-Datei zur Verfügung...
Und ja mir ist klar, dasss es sicher andere - vielleicht bessere Lösungsvarianten gibt, aber ich habe zurzeit kein neueres XL und keinerlei VBA-Kenntnisse. Außerdem hatte ich mich zufällig mit diesem Thema beschäftigt.