Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1300to1304
1300to1304
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellen untereinander auflisten

Forumthread: Zellen untereinander auflisten

Zellen untereinander auflisten
03.03.2013 16:45:46
Jennifer
Hallo alle zusammen,
ich hoffe ihr habt einen Rat, bzw. könnt mir viel Zeit beim Bearbeiten meiner Tabelle ersparen. Es geht um den Bereich D2:AN8237 meiner Tabelle. In diesem Bereich sind 1736 Zellen gefüllt, der Rest leer.
Mein Ziel ist es, die Inhalte der gefüllten Zellen untereinander in einem neuen Tabellenblatt aufzulisten, zu sortieren und Duplikate zu entfernen.
So nun zu meiner Frage, was ist der schnellste Weg dahin? Alle Spalten untereinander kopieren ist langwierig. Vor allem da ich davon ausgehe, dass bevor das Ganze fertig ist noch einige Spalten hinzukommen.
Danke für euren Rat
Jenny

Anzeige

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

Betreff
Datum
Anwender
Anzeige
AW: Zellen untereinander auflisten
03.03.2013 16:53:37
Hajo_Zi
Hallo Jenny,
Tabelle3
 A
111
211:00:00
320:00:00
419:00:00
518:00:00
617:00:00
716:00:00
815:00:00
914:00:00
1013:00:00
1111:00:00
1210:00:00
1309:00:00

verwendete Formeln
Zelle Formel Bereich
A1 =MAX(Tabelle2!D2:AN8237 ) 
A2 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A1;Tabelle2!$D$2:$AN$8237))} $A$2
A3 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A2;Tabelle2!$D$2:$AN$8237))} $A$3
A4 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A3;Tabelle2!$D$2:$AN$8237))} $A$4
A5 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A4;Tabelle2!$D$2:$AN$8237))} $A$5
A6 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A5;Tabelle2!$D$2:$AN$8237))} $A$6
A7 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A6;Tabelle2!$D$2:$AN$8237))} $A$7
A8 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A7;Tabelle2!$D$2:$AN$8237))} $A$8
A9 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A8;Tabelle2!$D$2:$AN$8237))} $A$9
A10 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A9;Tabelle2!$D$2:$AN$8237))} $A$10
A11 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A10;Tabelle2!$D$2:$AN$8237))} $A$11
A12 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A11;Tabelle2!$D$2:$AN$8237))} $A$12
A13 {=MAX(WENN(Tabelle2!$D$2:$AN$8237<A12;Tabelle2!$D$2:$AN$8237))} $A$13
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Tabellendarstellung in Foren Version 5.51



Anzeige
AW: Zellen untereinander auflisten
03.03.2013 16:57:57
Jennifer
Hallo Hajo,
erstmal danke.
sorry da hab ich wohl nicht ausführlich genug das Problem beschrieben. Die Zellen sind mit Text gefüllt nicht mit Zahlen, deine Formeln geben mir ausschließlich 0 aus.
Liebe Grüße
Jenny

AW: Zellen untereinander auflisten
03.03.2013 17:44:11
Hajo_Zi
Hallo Jenny,
wir können nur auf das Antworten was im Beitrag steht und was nicht steht wird angenommen.
Mache es mit VBA. 
Option Explicit
Sub Kopie()
Dim RaZelle As Range
Dim RaFound As Range
Dim Loletzte As Long
With Worksheets("Tabelle3")
For Each RaZelle In Worksheets("Tabelle2").Range("D2:AN8237")
If RaZelle  "" Then
Set RaFound = .Columns(1).Find(RaZelle, .Range("A" & Rows.Count), _
xlFormulas, xlWhole, , xlNext)
If RaFound Is Nothing Then
Loletzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
.Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
.Cells(Loletzte, 1) = RaZelle
End If
End If
Next RaZelle
.Columns(1).Sort Key1:=.Range("A3"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub
Gruß Hajo

Anzeige
AW: Zellen untereinander auflisten
03.03.2013 18:47:05
Jennifer
Hallo Hajo,
deine Version funktioniert fast. Vielen Dank.
Sie funktioniert, solange die Tabellen Tabelle1 usw. heißen, die Original Tabellennamen sind jedoch nach Personen benannt, haben also auch Leerzeichen. Wenn ich die Original Tabellennamen nehme, bekomme ich die Meldung Index außerhalb des gültigen Bereichs.
Gruß
Jenny

Anzeige
AW: Zellen untereinander auflisten
03.03.2013 18:52:04
Hajo_Zi
Hallo Jenny,
ich habe Deine Datei nicht gesehen, darum habe ich meine Namen genommen. Die musst Du anpassen.
Gruß Hajo

mit arrayList
03.03.2013 18:19:10
CitizenX
Hi,
Code kommt in ein allgemeines Modul,Tabellennamen bitte anpassen:
Option Explicit
Sub SortMe()
Dim oSort As Object, arr, z&, s&, out
Set oSort = CreateObject("System.collections.arraylist")
'Tabellenname Daten-ggf anpassen
arr = Sheets("Tabelle1").Range("D2:AN8237 ").Value
For z = LBound(arr, 1) To UBound(arr, 1)
For s = LBound(arr, 2) To UBound(arr, 2)
If isText(arr(z, s)) Then
If Not oSort.contains(arr(z, s)) Then oSort.Add arr(z, s)
End If
Next s
Next z
oSort.Sort
out = oSort.toArray
'Tabellenname Ausgabe-ggf anpassen
Sheets("Tabelle2").Cells(1, 1).Resize(oSort.Count, 1) = Application.Transpose(out)
End Sub
Function isText(ByVal myVal As String) As Boolean
If Len(Trim$(myVal)) Then
isText = Not IsNumeric(myVal)
Else
isText = False
End If
End Function
Grüße
Steffen

Anzeige
Sort ohne Dubletten - VBA
03.03.2013 18:25:03
Erich
Hi Jennifer,
gerade habe ich gesehen, dass auch Steffen in der Gegend Collections, ArrayList unterwegs war..
Vielleicht pronierst du das hier trotzdem mal aus: 

Option Explicit
Public Sub SortOhneDups_ArrayList()
' nach: ComboBox ohne Duplikate füllen - von ransi am 23.01.2009 13:39:41
' www.herber.de/forum/archiv/1040to1044/t1042357.htm#1042976
Dim colAL As Object, zz As Long, cc As Long, arrQ
arrQ = Sheets("Tabelle1").Range("D2:AN8237")    ' anpassen oder dynamisch ermitteln
Set colAL = CreateObject("System.Collections.ArrayList")
With colAL
For zz = 1 To UBound(arrQ)
For cc = 1 To UBound(arrQ, 2)
If Not IsEmpty(arrQ(zz, cc)) Then
If Not .contains(CStr(arrQ(zz, cc))) Then .Add CStr(arrQ(zz, cc))
End If
Next cc
Next zz
.Sort
Worksheets.Add
Cells(2, 1).Resize(UBound(arrE)) = Application.Transpose(.toArray)
End With
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Sort ohne Dubletten - Korrektur
03.03.2013 18:33:56
Erich
Hi,
da ist mir beim Vereinfachen eine Zeile durchgerutscht - arrE gibt es gar nicht mehr.
Bitte ersetze die Zeile
Cells(2, 1).Resize(UBound(arrE)) = Application.Transpose(.toArray)
durch
Cells(2, 1).Resize(UBound(.toArray) + 1) = Application.Transpose(.toArray)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Sort ohne Dubletten - Sorry - 2. Korrektur
03.03.2013 18:40:11
Erich
Hi,
das geht besser ohne UBound:
Cells(2, 1).Resize(.Count) = Application.Transpose(.toArray)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Zellen untereinander auflisten
03.03.2013 18:57:32
Jennifer
Hallo, ich denke, ich schreibe mal eine allgemeine Antwort an euch 3.
Ich gebe Hajo recht, ich muss natürlich die Namen der Tabellenblätter anpassen, jedoch bin ich anscheinend überfragt, wie, solange ich Namen wie Tabelle1 Tabelle2 oder so benutze, funktionieren alle mir vorgeschlagenen Makros, aber sobald ich einen eigenen Tabellennamen nehmen will nicht mehr, was vermute ich an dem Leerzeichen liegt. Ich nehme mal Hajos Vorschlag als Beispiel, wenn ich aus
For Each RaZelle In Worksheets("Tabelle2").Range("D2:AN8237")
For Each RaZelle In Worksheets("Jenny S").Range("D2:AN8237") mache kommt in seinem Vorschlag Index außerhalb des gültigen Bereichs.
Wenn ich den Namen entsprechend in CitizenX 's Vorschlag ändere bringt er mir den Laufzeitfehler 13.
Außerdem noch eine Frage an Erich, wo kann ich in deinem Beispiel die Zieltabelle angeben? oder ist die in deinem Beispiel auf die aktive Tabelle festgelegt?
Gruß
Jenny

Anzeige
AW: Zellen untereinander auflisten
03.03.2013 19:05:06
Hajo_Zi
hallo Jenny,
wie schon geschrieben sehen wir Deine Datei nicht.
Gruß Hajo

AW: Zellen untereinander auflisten
03.03.2013 19:13:57
Hajo_Zi
hallo Jenny,
wie schon geschrieben sehen wir Deine Datei nicht.
Gruß Hajo

Anzeige
AW: Zellen untereinander auflisten
03.03.2013 19:25:51
Erich
Hi Jennifer,
dein Problem mit den Leerzeichen in den TabNamen kann ich nicht nachvollziehen.
Bist du wirklich sicher, dass die Blattnamen mit den in der Prozedur verwendeten Namen übereinstimmen?
Du könntest die Namen (per Doppelklick in die Excel-Blattliste) markieren, kopieren und in die VBA-Prozedur einfügen.
Die Zieltabelle wurde in meinem Beispiel immer neu angelegt (und war dann die aktuelle).
Du hast eingangs geschrieben: "untereinander in einem neuen Tabellenblatt". Da hab ich mich nach gerichtet...
Hier nun wird in ein bestehendes Blatt geschrieben. Beide Blattnamen enthalten Leerrzeichen,
und es geht trotzdem: ;-) 

Public Sub SortOhneDups_ArrayList2()
' nach: ComboBox ohne Duplikate füllen - von ransi am 23.01.2009 13:39:41
' www.herber.de/forum/archiv/1040to1044/t1042357.htm#1042976
Dim colAL As Object, zz As Long, cc As Long, arrQ
arrQ = Sheets("Tab   1").Range("D2:AN8237")    ' anpassen oder dynamisch ermitteln
Set colAL = CreateObject("System.Collections.ArrayList")
With colAL
For zz = 1 To UBound(arrQ)
For cc = 1 To UBound(arrQ, 2)
If Not IsEmpty(arrQ(zz, cc)) Then
If Not .contains(CStr(arrQ(zz, cc))) Then .Add CStr(arrQ(zz, cc))
End If
Next cc
Next zz
.Sort
'      Worksheets.Add ' legte bisher ein neues Tabellenblatt an
Sheets("Tab   Erg").Cells(2, 1).Resize(.Count) = Application.Transpose(.toArray)
End With
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Zellen untereinander auflisten
03.03.2013 19:40:03
Jennifer
Hallo Erich, ganz zu Anfang als ich das geschrieben hatte, bin ich noch von ner Formellösung ausgegangen. Das Neue sollte dabei nur heißen, dass ich nicht vorhatte, die Auflistung in das Originaltabellenblatt zu schreiben, sondern vorhabe die Formel in ein neues Blatt einzufügen.

AW: Zellen untereinander auflisten
03.03.2013 19:36:49
Jennifer
Hallo ihr 3,
ich habe zwar keine Ahnung weshalb, aber ich habe nochmal die Sicherungskopie genommen, die ich gemacht hatte bevor ich eure Makros ausprobiert habe und siehe da jetzt kommen bei allen 3 Makros keine Fehlermeldungen mehr, auch nicht wenn ich die Tabellennamen ändere.
Vielen Dank auf jeden Fall und noch einen schönen Sonntagabend
Jenny
Anzeige
;

Forumthreads zu verwandten Themen

Inhalte mehrer Spalten untereinander auflisten?
Marc

Hallo,
ich habe folgendes Anliegen:
Ich habe eine Tabelle in der in einer Spalte Artikel (A1) stehen und in den beiden Spalten(B;C) dahinter verschiedene Varianten in Form von Artikelnummern. Mein Ziel ist es nun in einer neuen Tabelle die Varianten in einer Spalte untereinander aufzuli...
25.09.2018 15:13:18
Thread-ID: 1647674
2 Beiträge
alle Werte mit Sverweis auflisten
Christian
Hallo Forum,

ich möchte mit einem Sverweis nach z.B. "Produkt X" suchen, das Produkt kommt 3 mal in Spalte x vor. Nun möchte ich alle Spalten Y der Zeilen ausgeben, wo Produkt X drinsteht.
Beim normalen Sverweis bekommt man ja nur den ersten Treffer, ich bräuchte aber alle Treffer.
Dan...
30.10.2003 12:09:40
Thread-ID: 330993
5 Beiträge
Anzeige
Index alle Werte auflisten mit Kriterium
Bastian

Hallo liebe Community,
ich habe ein Problem mit einer Index Funktion. Ich habe eine Liste, welche Behälter, deren Wegstrecken und die Lager beinhaltet.
Über eine Index Funktion möchte ich mir alle Materialien anzeigen lassen, für welche der Behälter genutzt werden.
Durch eine Dropdo...
04.05.2021 19:32:10
Thread-ID: 1829555
5 Beiträge
Spalteninhalte untereinander auflisten
erichm
Hallo,
ich habe einen Bereich mit 3 Spalten (G, H und I) und ab Zeile 2. Die Zeilen sind unterschiedlich lang belegt; dies ergibt sich aus der Spalte A. In der Zelle A1 stelle ich fest, wie viele Zeilen belegt sind.
Jetzt sollen in der Spalte L, die Spalten G, H und I untereinander aufgelist...
15.01.2022 06:53:50
Thread-ID: 1865507
34 Beiträge
Werte aus Zeilen untereinander Auflisten und Adresse zufügen
Martin
Hallo zusammen,

ich habe eine Fragen, kann ich mir den Text einer Zeile 2 (Zellen verbunden) und die darunter stehenden Zellen (Zeile 3) in einer Spalte auflisten.
Dazu sollen auch die Zellenadresse zugefügt werden.

Hintergrund, ich habe in Zeile 2 Artikel stehen (Artikel 1 = A2:...
25.07.2023 12:19:19
Thread-ID: 1937177
3 Beiträge
Werte untereinander auflisten
Chris
Hallo Forum,

ich habe in Spalte A unterschiedliche viele Werte (Berufe) stehen. Einige davon kommen nur einmal vor, andere mehrfach.
Wie kann ich per VBA alle Werte, die ein- und mehrfach vorkommen in Spalte B untereinander auflisten?


Chris
12.09.2023 09:31:42
Thread-ID: 1945772
3 Beiträge
Doppelte Werte finden und untereinander auflisten
Chris
Hallo zusammen,

ich habe ca. eintausend werte in Spalte A und B stehen. Beide Spalten möchte auf doppelte Einträge vergleichen. Jeder Eintrag, der doppelt vorhanden ist, soll in Spalte C untereinander aufgelistet werden. Ich habe dazu untenstehende Makro, welches mir den letzten doppelt vor...
25.09.2023 10:39:22
Thread-ID: 1947299
13 Beiträge
Per Kunden-Nr. alle Artikelnamen untereinander auflisten
FrankyBär
Hallo zusammen,

ich habe eine Tabelle (Verkaufsliste) mit Kunden-Nr., Artikelname1 bist Artikelname10 in einer Zeile. Ich möchte jetzt gerne, das ich per Kunden-Nr. alle Artikelnamen untereinander und auch keine doppelten Artikelnamen angezeigt bekomme. Habe es schon versucht mit der Filter...
13.08.2024 21:45:47
Thread-ID: 1988054
12 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Zellen in Excel untereinander auflisten

Schritt-für-Schritt-Anleitung

  1. Vorbereitung: Stelle sicher, dass du die Tabelle hast, aus der du die Zellen untereinander auflisten möchtest. In diesem Beispiel verwenden wir den Bereich D2:AN8237.

  2. VBA-Editor öffnen:

    • Drücke ALT + F11, um den VBA-Editor zu öffnen.
    • Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen.

  3. Code einfügen: Kopiere den folgenden VBA-Code in das Modul:

    Option Explicit
Sub Kopie()
   Dim RaZelle As Range
   Dim RaFound As Range
   Dim Loletzte As Long
   With Worksheets("Tabelle3")
       For Each RaZelle In Worksheets("Tabelle2").Range("D2:AN8237")
           If RaZelle <> "" Then
               Set RaFound = .Columns(1).Find(RaZelle, .Range("A" & Rows.Count), xlFormulas, xlWhole, , xlNext)
               If RaFound Is Nothing Then
                   Loletzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
                   .Cells(Loletzte, 1) = RaZelle
               End If
           End If
       Next RaZelle
       .Columns(1).Sort Key1:=.Range("A3"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
   End With
End Sub

  4. Anpassen der Tabellennamen: Stelle sicher, dass du die Namen der Tabellenblätter korrekt anpasst, um potenzielle Fehler zu vermeiden.

  5. Ausführen: Drücke F5, um das Makro auszuführen und die gefüllten Zellen untereinander aufzulisten.

Häufige Fehler und Lösungen

  • Fehler: "Index außerhalb des gültigen Bereichs":

    • Überprüfe, ob die Tabellennamen korrekt sind. Bei Namen mit Leerzeichen musst du Anführungszeichen verwenden, z.B. Worksheets("Mein Blattname").

  • Fehler: "Laufzeitfehler 13":

    • Dies kann auftreten, wenn du in deinem Code auf Daten zugreifst, die nicht den erwarteten Typ haben. Stelle sicher, dass die Zellen, die du abfragst, den richtigen Datentyp haben.

Alternative Methoden

Neben der VBA-Methode kannst du auch die folgende Formel verwenden, um Excel-Werte untereinander aufzulisten:

  1. Mit der Funktion UNIQUE (Excel 365):

    =UNIQUE(D2:AN8237)

  2. Mit Power Query:

    • Lade die Daten in Power Query, wähle die Spalten aus, die du auflisten möchtest, und benutze die Funktion "Unpivot".

Praktische Beispiele

  1. VBA-Beispiel ohne Duplikate: Verwende die ArrayList-Methode, um alle Werte untereinander aufzulisten und Duplikate zu entfernen:

    Sub SortMe()
   Dim oSort As Object, arr, z&, s&, out
   Set oSort = CreateObject("System.Collections.ArrayList")
   arr = Sheets("Tabelle1").Range("D2:AN8237").Value
   For z = LBound(arr, 1) To UBound(arr, 1)
       For s = LBound(arr, 2) To UBound(arr, 2)
           If isText(arr(z, s)) Then
               If Not oSort.contains(arr(z, s)) Then oSort.Add arr(z, s)
           End If
       Next s
   Next z
   oSort.Sort
   Sheets("Tabelle2").Cells(1, 1).Resize(oSort.Count, 1) = Application.Transpose(oSort.toArray)
End Sub

  2. Formelbeispiel: Um alle Werte zu extrahieren, kannst du die FILTER-Funktion verwenden (Excel 365):

    =FILTER(D2:AN8237, D2:AN8237 <> "")

Tipps für Profis

  • Nutze Option Explicit: Dies hilft, Fehler bei Variablen zu vermeiden, da alle Variablen deklariert werden müssen.
  • Verwende WorksheetFunction: Für komplexere Berechnungen ist es oft besser, die Excel-Funktionen über VBA zu nutzen.
  • Speichere regelmäßig: Vor allem, wenn du mit VBA arbeitest, ist es ratsam, regelmäßig Sicherungskopien deiner Datei zu erstellen.

FAQ: Häufige Fragen

1. Wie kann ich die Formel anpassen, wenn ich mehrere Blätter habe? Du musst die Blattnamen im Code entsprechend anpassen, um alle Daten korrekt zu referenzieren.

2. Gibt es eine Möglichkeit, die Liste automatisch zu aktualisieren? Ja, du kannst das Makro so anpassen, dass es bei jeder Änderung in den Quelldaten automatisch ausgeführt wird, indem du das Worksheet-Change-Ereignis nutzt.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige