Microsoft Excel

Herbers Excel/VBA-Archiv

Verketten / Concatenate über gesamte Spalte

Betrifft: Verketten / Concatenate über gesamte Spalte von: Timmie
Geschrieben am: 11.08.2014 16:25:14

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,

  

Betrifft: AW: Verketten / Concatenate über gesamte Spalte von: Daniel
Geschrieben am: 11.08.2014 16:42:54

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


  

Betrifft: Viell solltest du doch mal den RECHERCHE-... von: Luc:-?
Geschrieben am: 11.08.2014 16:48:44

…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 :-?


  

Betrifft: Bspw findest du 'MxJoin' ... von: Luc:-?
Geschrieben am: 11.08.2014 18:36:47

hier! Luc :-?


  

Betrifft: nur mit VBA oder reichlich Hilfsspalten von: ransi
Geschrieben am: 11.08.2014 18:04:47

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


  

Betrifft: AW: nur mit VBA oder reichlich Hilfsspalten von: Timmie
Geschrieben am: 11.08.2014 18:57:17

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


  

Betrifft: AW: nur mit VBA oder reichlich Hilfsspalten von: Daniel
Geschrieben am: 11.08.2014 19:09:45

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


  

Betrifft: AW: nur mit VBA oder reichlich Hilfsspalten von: Timmie
Geschrieben am: 11.08.2014 19:20:33

OK, vielen Dank.


  

Betrifft: Tut mir leid. Code vergessen.... von: ransi
Geschrieben am: 11.08.2014 22:56:57

HAllo

Hier die Beispieltabelle:
https://www.herber.de/bbs/user/92027.xlsm


ransi


  

Betrifft: 'Verketten2' hat Ransi ja auch gar nicht ... von: Luc:-?
Geschrieben am: 11.08.2014 19:35:13

…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 :-?


  

Betrifft: AW: 'Verketten2' hat Ransi ja auch gar nicht ... von: Daniel
Geschrieben am: 11.08.2014 20:01:13

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


  

Betrifft: Ja, hab ich, weil er nichts dazu geschrieben ... von: Luc:-?
Geschrieben am: 11.08.2014 20:48:25

…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 :-?


  

Betrifft: AW: Ja, hab ich, weil er nichts dazu geschrieben ... von: Daniel
Geschrieben am: 11.08.2014 21:24:53

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?


  

Betrifft: AW: Ja, hab ich, weil er nichts dazu geschrieben ... von: Ewald
Geschrieben am: 12.08.2014 01:08:36

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


  

Betrifft: Entschuldigung, falsche Behauptung? Na, ... von: Luc:-?
Geschrieben am: 12.08.2014 03:23:59

…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 :-?


  

Betrifft: AW: Entschuldigung, falsche Behauptung? Na, ... von: Daniel
Geschrieben am: 12.08.2014 09:00:52

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


 

Beiträge aus den Excel-Beispielen zum Thema "Verketten / Concatenate über gesamte Spalte"