Wie kann ich in Excel viele Zellen einer Spalte miteinander verketten/ Gibt es die Möglichkeit bestimmte Bereiche automatisch zu einer Zelle zusammenzufügen?
Hallo, ich habe eine extrem lange Liste bestehen aus zwei Spalten. In Spalte A sind Zahlen und in Spalte B sind Wörter.
Frage 1: Wie kann ich bestimmte Bereiche in eine Zelle zusammenfügen; am besten durch Komma getrennt. Was ich bislang gefunden habe erscheint mir etwas kompliziert: VERKETTEN(MTRANS(B1:B67)) Dann kommt da sowas raus: {"Nähe"."Nu"."Uni"."Hahn"."Neue"."Huhn"."On"."Yen"."Nahe"."Hohn"."Ion"."Henne".} Hier mache ich nun mit Ersetzen die Anführungszeichen und die Klammern weg und aus dem Punkt ein Komma mit Leerzeile. Geht das nicht irgendwie noch leichter?
Frage 2: Könnte man auch gleich befehlen, dass alle Wörter, denen in Spalte A die gleiche Zahl zugeordnet ist, in einer Zelle zusammengefasst werden?
So sieht das ganze etwa aus:
A B
1 Eid
1 Hoheit
1 Hit
1 Heute
2 Huhn
2 Henne
3 Mühe
3 Oma
3 Heim
usw. Die Liste hat an die 270.000 Einträge (Zeilen). Die Zahlen in Spalte A gehen bis 99999. Daher sollte so viel wie möglich automatisch laufen. Denn fast einhunderttausend Einträge (so wie ich es bislang von Hand mache) zu ändern ist der Horror ... Ein Freund sagte mir, ich brauche wahrscheinlich ein Makro. Allerdings habe ich überhaupt keine Ahnung wie ich das verwende. Daher bitte ich um eine ausführliche Erklärung und hoffe, dass ich nicht zu viel verlange...
Vielen vielen lieben Dank schonmal. Ich hoffe und freue mich auf Antworten
4 Antworten
Ich hätte da eine Ansatz ohne Makro:
Angenommen Deine Liste beginnt in A1:B1, dann diese Formel in
C1: =B1
und diese Formel in
C2: =WENN(A2=A1;C1&" ,"&B2;B2)
und runterkopieren.
Dann diese Formel in
D1=WENN(A1=A2;"";C1)
und runterkopieren.
Dann sieht es so aus wie auf meinem Screenshot.
Weiter kannst Du dann Spalte D markieren und mit Kopieren --> Inhalte einfügen... -> Werte, den Text in D festschreiben.
Anschließend "Benutzerdefiniert Sortieren" nach Spalte D.
Jetzt kannst Du die Spalten B und C löschen und hast nach den ganzen Leerzelle die Liste, die Du haben willst.
Klappt es?
P.S.:
Verschiebe die Leerstelle noch hinter das Komma, dann ist es richtig:
=WENN(A2=A1;C1&", "&B2;B2)
Hast Du diesen Teil meiner Erläuterung beachtet:
Weiter kannst Du dann Spalte D markieren und mit Kopieren --> Inhalte einfügen... -> Werte, den Text in D festschreiben.
Wenn nicht, passiert eigentlich genau das, was Du beschreibst.
Falls Du das nicht verstehst, frage nochmal nach.
P.S.:
Alternativ kannst Du auch Spalte D markieren, auf "Filtern" gehen und das Häkchen bei "Leere" entfernen.
Dann kannst Du die Formeln stehen lassen.
Ist vielleicht sogar von Vorteil, weil Du das problemlos wieder rückgängig machen kannst und dann Zugriff auf die einzelnen Einträge hast usw.
Nein, ich habe nur kopiert und es eingefügt, was natürlich keinen Sinn ergab... :-)
Aber jetzt ist endlich alles wie vorgestellt.
Vielen vielen lieben Dank!
Eine Lösung mit Formeln fällt mir hier diese Anforderung nicht ein.
Wenn Du in Spalte C eine dublettenfreie Liste der Zahlen aus Spalte A stehen hast (Spalte A kopieren, in C einfügen und unter Daten > Duplikate entfernen) dann schreibt dieses Makro in Spalte D die passenden Worte hintereinander:
Sub sammeln()
Dim i As Long
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
Cells((WorksheetFunction.Match(Cells(i, 1).Value, Range("C1:C100"), 0)), 4).Value = Cells((WorksheetFunction.Match(Cells(i, 1).Value, Range("C1:C100"), 0)), 4).Value & Cells(i, 2).Value & ","
Next
End Sub
Den Bereich C1:C100 musst Du dann noch passend machen für die Anzahl Deiner Zahlen.
Zum Einfügen des Makros:
Ribbon Entwicklertools > Visual Basic
Dort zum Tabellenblatt, auf dem Deine Zahlen stehen reinkopieren.
F5 startet das Makro oder mit F8 Einzelschritte durchgehen.
Wo soll der Bereich mit den "Verketteten" Zellinhalten sein? Im selben Tabellenblatt an einer bestimmten separaten Stelle oder sind es mehrere Bereiche und wo sollen sie sein?
Die unter Spalte A stehenden Begriffe mit der "Zahl 3" soll dann folgendermaßen aussehen (?): Mühe, Oma, Heim, ...?
Heute Nacht schaffe ich das nicht, möchte aber gleich betonen, dass ich nicht total excelfit bin.
Also Excel-Freaks nach vorn!
Am liebsten hätte ich am Ende nur noch zwei Spalten mit:
(Spalte A) der Zahl und
(Spalte B) allen zugehörigen Begriffen zu dieser Zahl in einer Zelle
A B
- Eid, Hoheit, Hit, Heute
- Huhn, Henne
- Oma, Mühe, Heim
Heute Nacht brauche ich es auch nicht. Arbeite auch nur daran, weil ich noch nicht schlafen kann. Hat auch ein paar Tage Zeit. Arbeite nicht in letzter Sekunde.
Nur interessehalber: das ist eine Liste für das Mastersystem?
Ja genau. Versuche eine drei-, vier- und fünfstellige Liste zu erstellen. Habe aus einer Liste mit deutschen Wörtern alle Nomen herausgesucht. Die in die entsprechenden Zahlen umgewandelt und alle anderen überflüssigen Buchstaben entfernt und nun in zwei Spalten einmal die Zahl und dann das dazugehörige Wort. Da das alles unendlich lang ist, sollte es zu jeder Zahl nur eine Zelle mit allen Begriffen geben.
Die ein- und zweistelligen Zahlen sind nicht so wichtig, da habe ich auch bereits meine Liste mit der ich bislang gut zurechtkomme, das war gerade eher nur für die Erklärung was ich brauche. Wobei, man weiß ja nie, wann man sie mal braucht. Wenn sie schon da sind.
Ich habe das Problem, dass ich mir meine Wörter selbst erstellen muss, da ich in einem Kurs eine andere Zuordnung gelernt habe, als allgemein üblich und diese bereits in meinem Kopf ist. (0=SCH, TZ, Z / 1= D, T / 2= N / 3= M / 4= L / 5 = S, SS, ß / 6= G, K, CK / 7=F, V, W / 8= B, P / 9= R). Aber da die Zahlen-Buchstaben-Kombis nun schonmal so in meinem Kopf sind, würde ich wahrscheinlich nur durcheinander kommen.
Arbeitest du auch mit dem Mastersystem?
Hayns wrote:
Also Excel-Freaks nach vorn!
Nach dem sich diese also eingefunden haben, die Excel-Freaks, klinke ich ich mich aus, da das eine Nummer zu groß für meine bescheidenen Excelkenntnisse ist.
Werde das interessante Thema aber verfolgen - viel Erfolg.
Du willst 270000 Wörter aus Spalte B in einer einzigen Zelle verketten?
Nein, ich hätte gerne
alle Begriffe aus Spalte B, denen in Spalte A die Zahl 1 zugeordnet wurde, in einer Zelle.
Dann alle Begriffe aus Spalte B, denen in Spalte A die Zahl 2 zugeordnet wurde, in einer Zelle.
Dann alle mit Begriffe zu denen die 4, 5, 6 usw. gehört, zusammen.
Oje, muss ich mir erst mal ansehen, spontan hab ich keine Lösung.
Handicap:
in eine Zelle passen max. 32767 Zeichen und schon Zellen mit paar hundert Zeichen sind schwer zu handeln.
Nun ja. Zuerst wars klasse. Aber sobald ich die anderen beiden Spalten löschen will, steht in der andern nur noch BEZUG.
Aber ok, die könnte ich auch ausblenden. Vielen Dank für die Lösung!
Nur das Sortieren will mir einfach nicht gelingen. Könntest du mir das bitte nochmal erklären? Wenn ich nach D sortiere, habe ich immer noch leere zellen in Spalte D dazwischen.