Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.04.2024 21:55:37
Preiserhöhung über mehrere Jahre berechnen
17.04.2024 20:19:32
Endzeitpunkt berechnen aus d:hh:mm errechnen
17.04.2024 18:57:33
Errinnerungsfunktion
17.04.2024 16:56:58
Stunden addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1372to1376
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1372to1376
1372to1376
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Verketten / Concatenate über gesamte Spalte

Verketten / Concatenate über gesamte Spalte
11.08.2014 16:25:14
Timmie
Hallo zusammen,
ich habe folgendes Problem:
Ich habe ein Excel, bei der in Spalte A verschiedene Werte (Namen) stehen (oft auch derselbe Name mehrmals untereinander) und in Spalte B eine jeweilige Email Adresse.
Ich würde gern folgendes, banal klingendes Problem lösen, aber es erscheint doch schwieriger:
Ich würde gern in einer Ergebniszelle alle Email Adressen (in Spalte B) über den Befehl "Verketten" oder in meinem Excel "Concatenate" (englisches Excel) verbinden, wenn ein bestimmter Name in Spalte A auftaucht. In einer weteren Ergniszelle sollen dann alle Email-Adressen eines anderen Names stehen (idealerweise durch ein ";" getrennt, aber das wäre die Luxusversion).
Z.B. hat der Name "Meier" mehrere Email Adressen, die dann jeweils in B2, B3, B4 stehen. Der Name Meier steht entsprechend in A2, A3 und A4.
Leider habe ich festgestellt, dass "Concatenate" nur funktioniert, wenn man einzelne Zellen anklickt, einen Bereich kann man aber nicht markieren, der verkettet werden soll. Das macht es für mich schwierig, insbesondere wenn ich eine Matrixformel anwenden möchte, die dann nicht funktioniert.
Hat hier jemand eine Lösung (als eine Formel, ungern über mehrere Hilfsspalten)? Manuell ist keine Lösung, da es hier über mehr als 100 Zeilen laufen kann und auch Änderungen unterworfen ist.
Danke & VG,

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verketten / Concatenate über gesamte Spalte
11.08.2014 16:42:54
Daniel
Hi
eine Formel die sowas kann, gibt es in Excel noch nicht und es ist auch mit den vorhandenen Excelformeln nicht so ohne weiters darstellbar, Excel hat an dieser Stelle noch eine Schwachstelle, weil es als Tabellenkalkulation halt mehr auf die Verarbeitung von Zahlen ausgerichtet als auf die von Texten, ebenso ist Excel keine Datenbank, erst recht keine relationale (so wie es hier sinnvoll wäre).
man könnte sich ein entsprechendes Verketten oder VerkettenWenn natürlich in VBA nachbauen, aber bei VBA-Nein sicherlich auch nicht die beste Variante.
am einfachsten und Speicherplatzschonensten ist immer noch die Hilfspaltenlösung mit folgender Formel in Zelle C2 (und nach unten ziehen): =Wenn(A2=A3;B2&";"&C3;B2)
beim jeweils ersten Auftauchen des Namens stehen dann alle EMailadressen in einer Zelle, so dass du die Tabelle dann mit =SVerweis("Meier";A:C;3;0) auswerten kannst.
gruß Daniel

Anzeige
Viell solltest du doch mal den RECHERCHE-...
11.08.2014 16:48:44
Luc:-?
…Button bemühen, Timmie,
denn dazu gibt's jede Menge im Archiv, zB die (in Fmln einsetzbaren) UDFs Verketten2, MxJoin u.a., auch einige Subprozeduren dazu.
VERKETTEN (Concatenate) verbindet stets nur seine explizit angegebenen Argumente miteinander, und zwar ggf elementweise, niemals die Elemente eines Arguments untereinander! So fktt nur die vbFkt Join, allerdings nur für horizontal gerichtete vbVektoren, denn nur die sind normalerweise 1dimensional.
Gruß, Luc :-?

Bspw findest du 'MxJoin' ...
11.08.2014 18:36:47
Luc:-?
…hier! Luc :-?

Anzeige
nur mit VBA oder reichlich Hilfsspalten
11.08.2014 18:04:47
ransi
Hallo Timmi
Ohne VBA wirst du da nicht zum Ziel kommen.
Teste mal dies:
Tabelle1

 ABCDEF
1MüllerImähl@irgendwas.xy1  MüllerImähl@irgendwas.xy1, Imähl@irgendwas.xy12, Imähl@irgendwas.xy16, Imähl@irgendwas.xy22
2SchmidtImähl@irgendwas.xy2  SchmidtImähl@irgendwas.xy2, Imähl@irgendwas.xy13, Imähl@irgendwas.xy17, Imähl@irgendwas.xy23
3SchneiderImähl@irgendwas.xy3  SchneiderImähl@irgendwas.xy3, Imähl@irgendwas.xy14, Imähl@irgendwas.xy18, Imähl@irgendwas.xy24
4FischerImähl@irgendwas.xy4  FischerImähl@irgendwas.xy4, Imähl@irgendwas.xy15, Imähl@irgendwas.xy19, Imähl@irgendwas.xy25
5WeberImähl@irgendwas.xy5  WeberImähl@irgendwas.xy5, Imähl@irgendwas.xy20, Imähl@irgendwas.xy26
6MeyerImähl@irgendwas.xy6  MeyerImähl@irgendwas.xy6, Imähl@irgendwas.xy21, Imähl@irgendwas.xy27
7WagnerImähl@irgendwas.xy7  WagnerImähl@irgendwas.xy7, Imähl@irgendwas.xy28
8BeckerImähl@irgendwas.xy8  BeckerImähl@irgendwas.xy8
9SchulzImähl@irgendwas.xy9  SchulzImähl@irgendwas.xy9
10HoffmannImähl@irgendwas.xy10  HoffmannImähl@irgendwas.xy10
11SchäferImähl@irgendwas.xy11  SchäferImähl@irgendwas.xy11
12MüllerImähl@irgendwas.xy12  MayerImähl@irgendwas.xy29
13SchmidtImähl@irgendwas.xy13  HerrmannImähl@irgendwas.xy30
14SchneiderImähl@irgendwas.xy14  KöhlerImähl@irgendwas.xy31
15FischerImähl@irgendwas.xy15  WalterImähl@irgendwas.xy32
16MüllerImähl@irgendwas.xy16  KönigImähl@irgendwas.xy33
17SchmidtImähl@irgendwas.xy17  SchulzeImähl@irgendwas.xy34
18SchneiderImähl@irgendwas.xy18  FuchsImähl@irgendwas.xy35
19FischerImähl@irgendwas.xy19  #BEZUG!#WERT!
20WeberImähl@irgendwas.xy20  #BEZUG!#WERT!
21MeyerImähl@irgendwas.xy21  #BEZUG!#WERT!
22MüllerImähl@irgendwas.xy22  #BEZUG!#WERT!
23SchmidtImähl@irgendwas.xy23  #BEZUG!#WERT!
24SchneiderImähl@irgendwas.xy24  #BEZUG!#WERT!
25FischerImähl@irgendwas.xy25    
26WeberImähl@irgendwas.xy26    
27MeyerImähl@irgendwas.xy27    
28WagnerImähl@irgendwas.xy28    
29MayerImähl@irgendwas.xy29    
30HerrmannImähl@irgendwas.xy30    
31KöhlerImähl@irgendwas.xy31    
32WalterImähl@irgendwas.xy32    
33KönigImähl@irgendwas.xy33    
34SchulzeImähl@irgendwas.xy34    
35FuchsImähl@irgendwas.xy35    
36      

Formeln der Tabelle
ZelleFormel
E1=INDEX(Liste_Unikate($A$1:$A$1000); ZEILE(A1))
F1=verketten2(E1;$A$1:$A$1000;$B$1:$B$1000)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi

Anzeige
AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 18:57:17
Timmie
Super, vielen Dank hierfür (und auch alle anderen Antwortenden), das sieht schon sehr nach dem aus, was ich gesucht habe.
hierbei noch eine Frage: Die Funktion "Verketten2" ist mir total unbekannt, kenne nur das normale "verketten". Ich hatte eben versucht die englisch übersetzung dafür zu googeln und bin komplett gescheitert, finde nur übersetzungen wo nur "verketten" auftaucht. Kennst Du die englische Funktion? Ich hoffe, mein Excel ist nicht veraltet.
Danke & VG

AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 19:09:45
Daniel
Hi
Verketten2 ist keine originäre Excelfunktion.
Hier hat jemand in VBA eine solche Funktion geschrieben so gestaltet, dass man diese wie eine normale Excelfunktion verwenden kann.
Deswegen schreibt ransi ja auch "nur mit VBA oder vielen Hilfsspalten"
Den Code für Verketten2 mit zu posten hat er jetzt leider vergessen.
Wie es ohne VBA und mit nur einer Hilfsspalten mit einer einfachen Formel funktionieren kann, wenn die Daten sortiert sind, wurde dir hier ja schon gezeigt.
Gruß Daniel

Anzeige
AW: nur mit VBA oder reichlich Hilfsspalten
11.08.2014 19:20:33
Timmie
OK, vielen Dank.

'Verketten2' hat Ransi ja auch gar nicht ...
11.08.2014 19:35:13
Luc:-?
…erwähnt, Daniel,
und Timmie scheint ja die Alternative MxJoin, trotz Link, gar nicht zu interessieren…
Es ist doch immer wieder eine große Unkenntnis bzgl der Einsatz­möglichkeiten und -weise von UDFs feststellbar — nicht nur bei Level „VBA nein“!
Luc :-?

AW: 'Verketten2' hat Ransi ja auch gar nicht ...
11.08.2014 20:01:13
Daniel
Sorry Luc,
Ransi hat Verketten2 in der Spalte F seiner Beispieltabelle verwendet.
In der Jeanie-Darstellung werden die Formeltexte in der zweiten kleineren Tabelle unterhalb der grossen angezeigt.
Hast du die vielleicht übersehen?
Gruß Daniel

Anzeige
Ja, hab ich, weil er nichts dazu geschrieben ...
11.08.2014 20:48:25
Luc:-?
…hatte, Daniel,
und posten muss er den Code auch nicht, denn im Ggsatz zu MxJoin wimmelt das Archiv nur so von Verketten2 diverser Autoren. Die älteste Version könnte die von Sepp E. (2005/02+03) sein (VerkettenM dann 2010/06), eine von NoNet (2010/06), Jackd (2013/05), ANdreas (2005/09) usw. Auch ein VerkettenB kommt vor und wer weiß, was nicht noch alles. Ich habe auch mehrere UDFs und Subprozz geschrieben, die so etwas, zT als NebenEffekt, machen, von denen die Eine oder Andere auch im Archiv zu finden sein könnte.
Luc :-?

AW: Ja, hab ich, weil er nichts dazu geschrieben ...
11.08.2014 21:24:53
Daniel
Dann lies zukünftig die Beiträge besser, damit du nicht wieder falsche Behauptungen in die Welt setzt.
Sollte dir das trotzdem mal passieren, wäre eine kleine Entschuldigung angebracht, findest du nicht auch?

Anzeige
AW: Ja, hab ich, weil er nichts dazu geschrieben ...
12.08.2014 01:08:36
Ewald
Hallo,
reicht das vielleicht
 ABCDEF
1MüllerAdresse1 Adresse1;Adresse4;Adresse7  
2MeierAdresse2 Adresse2;Adresse5;Adresse8  
3ScmidtAdresse3 Adresse3;Adresse6;Adresse9  
4MüllerAdresse4 Adresse1;Adresse4;Adresse7  
5MeierAdresse5 Adresse2;Adresse5;Adresse8  
6ScmidtAdresse6 Adresse3;Adresse6;Adresse9  
7MüllerAdresse7 Adresse1;Adresse4;Adresse7  
8MeierAdresse8 Adresse2;Adresse5;Adresse8  
9ScmidtAdresse9 Adresse3;Adresse6;Adresse9  

ZelleFormel
D1=SVERWEIS2(A1;$A$1:$B$9;1;2;0;";")
D2=SVERWEIS2(A2;$A$1:$B$9;1;2;0;";")
D3=SVERWEIS2(A3;$A$1:$B$9;1;2;0;";")
D4=SVERWEIS2(A4;$A$1:$B$9;1;2;0;";")
D5=SVERWEIS2(A5;$A$1:$B$9;1;2;0;";")
D6=SVERWEIS2(A6;$A$1:$B$9;1;2;0;";")
D7=SVERWEIS2(A7;$A$1:$B$9;1;2;0;";")
D8=SVERWEIS2(A8;$A$1:$B$9;1;2;0;";")
D9=SVERWEIS2(A9;$A$1:$B$9;1;2;0;";")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Funktion (auch von Ransi)
Public Function SVERWEIS2(Kriterium As String, _
Bereich As Range, _
SuchSpalte As Integer, _
ErgebnissSpalte As Integer, _
Optional Unikate As Boolean = True, _
Optional Trenner As String = ", ") As String
Dim arrTmp
Dim l As Long
Dim Mydic As Object
arrTmp = Bereich
Set Mydic = CreateObject("Scripting.Dictionary")
If Unikate = True Then
For l = 1 To UBound(arrTmp)
If arrTmp(l, SuchSpalte) = Kriterium Then Mydic(arrTmp(l, ErgebnissSpalte)) = 0
Next
SVERWEIS2 = Join(Mydic.keys, Trenner)
Else:
For l = 1 To UBound(arrTmp)
If arrTmp(l, SuchSpalte) = Kriterium Then Mydic(l) = arrTmp(l, ErgebnissSpalte)
Next
SVERWEIS2 = Join(Mydic.Items, Trenner)
End If
End Function
Gruß Ewald

Anzeige
Entschuldigung, falsche Behauptung? Na, ...
12.08.2014 03:23:59
Luc:-?
…du scheinst mir ja auf einem sehr hohen Ross zu sitzen, fall bloß nicht runter…
Die Rolle des „Wadenbeißers“ scheint dir ja schon mal zu liegen… :->>
Wann hast du dich übrigens mal entschuldigt…?
Und meine „Behauptung“ war per sé nicht falsch, denn Ransi hat eine dieser Fktt zwar verwendet (ggf in Visualisierung meiner Bemerkung), aber das tatsächlich nicht erwähnt, sondern nur allgemein VBA. Eine andere Sichtweise grenzt ja wohl an „Krümel­k…ei“!
Luc :-?

AW: Entschuldigung, falsche Behauptung? Na, ...
12.08.2014 09:00:52
Daniel
Hi luc
naja, wer hier der Wadenbeisser ist, ergibt sich ja aus der Reihenfolge der Beiträge.
Wenn Ransi schreibt, das er in der Zelle F1 die Formel "=Verketten2" verwendet, dann hat er Verketten2 erwähnt.
Das ist keine Kekskrümelei, sondern einfach eine Tatsache und dann kannst du nicht einfach das Gegenteil behaupten.
Gruß Daniel
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige