Namen aus einer Geburtstags Tabelle kopieren und zusammenfügen?
Hallo Leute,
ich habe eine Geburtstags Tabelle:
Spalte B4 bis B62 Geburtstag z.B. 01.01.2020
Spalte C4 bis C62 Namen z.B. Klaus Mustermann
Ich möchte alle Namen mit dem Gleichen Geburtstag (Tag & Monat) in eine Zelle Kopieren.
Hat da jemand eine Idee?
Kann mir da bitte jemand helfen.
Welche Excelversion kommt zu Einsatz mit Filter + Matrixzutext ist das kein größeres Problem.
Ich nutze Office 365
2 Antworten
Neue Variante (mit intelligenter Tabelle)
So wie versprochen hier die Umsetzung es war nichts weiter nötig als die Ursprünglichen Zellbezüge gegen die Spaltennamen auszutauschen, ich habe aber die Gelegenheit ebenso noch genutzt und die Formeln ein wenig optimiert, Fokus lag in diesem Falle auf der Lesbar und Wartbarkeit.
Die Formel in blau ist nun deutlich kürzer und nicht mehr ganz so verzwickt, diese liest jetzt nur noch Monat und Datum der Spalte Geburtsdatum aus und wandelt es anschließend in ein Datum um (bedeutet es trägt automatisch das Jahr 2024, daher habe ich als Zellenformatierung für die Spalte E TT.MM gewählt. Das Datum hat aber den Vorteil, dass ich dies einfach aufsteigend sortieren kann ohne nochmal Datum und Tag zu separieren.
Die gleiche Logik wende ich auch bei den Formeln in orange und grün an, ich denke sollte einer mal die Formel anpacken müssen, kann man dies nun ohne fremde Hilfe auch gut tun, da sie nun eigentlich recht verständlich ist.
Außerdem habe ich die Matrixzutext Variante entfernt und eine einfache Wenn Version genommen, bei großen Datenmengen würde ich aber auf jeden Fall auf die Filter Funktion zurückgreifen. Diese sollte performanter sein, als Wenn-Dann.
=SORTIEREN(DATWERT(EINDEUTIG(TEXT(Tabelle2[Geburtdatum];"TT.MM"))))
-
=TEXTVERKETTEN(" / ";1;WENN(DATWERT(TEXT(Tabelle2[Geburtdatum];"TT.MM"))=E2;Tabelle2[Vorname]&", "&Tabelle2[Nachname];""))
-
=TEXTVERKETTEN(" / ";1;FILTER(Tabelle2[Nachname]&", "&Tabelle2[Vorname];DATWERT(TEXT(Tabelle2[Geburtdatum];"TT.MM"))=E2))
Alte Lösung:
Über die lange EINDEUTIG Formel in Blau holst du dir sortiert nur Monat und Tag des Geburtsdatums ab. (sicher geht die auch kürzer, aber auf die schnelle war das der beste weg).
Im Anschluss werden entweder über die Formel in orange oder in grün die Namen aneinander gereiht, die an dem Tag Geburtstag haben.
=SORTIERENNACH(EINDEUTIG(VERKETTEN(TAG(A2:A21);".";MONAT(A2:A21)));TEXTNACH(EINDEUTIG(VERKETTEN(TAG(A2:A21);".";MONAT(A2:A21)));".");1;TEXTVOR(EINDEUTIG(VERKETTEN(TAG(A2:A21);".";MONAT(A2:A21)));".");1)
-
=MATRIXZUTEXT(FILTER(VERKETTEN($C$2:$C21;", ";$B$2:$B$21);VERKETTEN(TAG($A$2:$A$21);".";MONAT($A$2:$A$21))=E2;"");0)
-
=TEXTVERKETTEN(" / ";WAHR;FILTER(VERKETTEN($C$2:$C$21;", ";$B$2:$B$21);VERKETTEN(TAG($A$2:$A$21);".";MONAT($A$2:$A$21))=E2;""))
Ja hat geklappt. Funktioniert :-)
Nutze die Erste und die Letzte Formel.
Kann ich die Formeln auch mit Tabellen (Tabellenspalten) nutzen?
Da bekomme ich immer nur #Werte Angezeigt :-(
Was genau meinst du mit Tabellenspalten sowas wie A:A oder Tabelle1[Geburtsdatum] also eine intelligente Tabelle?
Sollte eigentlich gehen wüsste zumindest jetzt nicht warum nicht, aber prüfe es mal nach um es zu reproduzieren.
So hab die Variante nochmal angepasst und ist nun mit intelligenten Tabellen kompatibel, ich weiß ja nicht wie deine Daten vorliegen ob alle in einer Spalte oder weil du sagst eine dynamischer Kalender, ggf. müssen dies erst mit mit VSTAPELN zu einer Matrix kombiniert werden.
Ja Funktioniert super. :-)
War ein Fehler von mir.
Danke für die schnelle Unterstüzung.
So was braucht vermutlich ein VBA Makro. Theoretisch wäre es auch mit einer Formel machbar, die aber sehr komplex würde, weil es vermutlich verschiedene Möglichkeiten bei der Anzahl von Namen gibt. Leider habe ich gerade kein Excel (es geht doch um Excel?) zur Hand, um es mal zu probieren.
Ansonsten geht es auch "von Hand", was dann aber nicht dynamisch ist und beispielsweise bei zusätzlichen Einträgen wiederholt werden muss. Ich würde wie folgt vorgehen:
1. Hilfsspalte mit Geburtstage ohne Jahr erstellen, bspw. in Zelle D4: =TEXT(MONAT(B4);"00")&TEXT(TAG(B4);"00"). So erhältst du einen String für den Geburtstag, bspw. "1101" für den 1. November.
2. Filter auf der neuen Spalte erstellen
3. Nach der Hilfsspalte D sortieren
4. Die Namen je Geburtstag "rausholen", bspw. mit =C4&", "&C5 etc.
Ich möchte die Funktion in einem Dynamischen Kalender benutzen den ich schon habe. Ich denke da würde es über Makro mehr sinn machen.
Ja, wenn es dynamisch sein soll, macht es über ein Makro mehr Sinn. Leider bin ich gerade auf Reisen und habe kein Excel zur Hand, um dir weiter zu helfen, sorry.
Danke. Ich werde das mal in dem Kalender testen :-)