Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Duplikate entfernen und Werte gleichzeitig sortier

Duplikate entfernen und Werte gleichzeitig sortier
18.03.2017 21:00:01
Christian
Moin und vielen Dank für die Aufnahme ins Forum. Meine VBA Kenntnisse würde ich aktuell noch als rudimentär bezeichnen aber ich bin motiviert und kämpfe mich durch. In meiner Abschlussarbeit für die Uni habe ich ein VBA Problem weswegen ich vielleicht in nächster Zeit noch einige Posts abgeben werde.
Nun zu meiner aktuellen Herausforderung:
Ich habe zwei Tabellen, die eine tbl_UAE01, da liegen die Werte in einer Spalte. Bei den Werten sind viele Duplikate dabei und Sie sind unsortiert.
Zweite Tabelle tbl_test3. Da sollen die Werte hin, sortiert und die duplikate entfernt werden.
Problem: Bei .Range("C1:C800").RemoveDuplicates Columns:=1, Header:=xlNo kommt die Fehlermeldung. Objekt erforderlich
Mein bisheriger Code:
Sub DuplikateEntfernen()
Dim Zeile As Long
'    Dim Zeilemax As Long
'SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With tbl_test3
With tbl_UAE01
'      Zeilemax = .UsedRange.Rows.Count
For Zeile = 2 To 800
.Range("H2:H800").Copy Destination:=Worksheets("tbl_test3").Range("C1")
Next Zeile
End With
.Range("C1:C800").RemoveDuplicates Columns:=1, Header:=xlNo
.SortMethod = xlPinYin
.Apply
End With
End Sub
Ich bedanke mich jetzt schon mal ganz herzlich und freue mich auf alle Anregungen, Ideen
Viele Grüße
Christian
Anzeige

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Duplikate entfernen und Werte gleichzeitig sortier
18.03.2017 21:04:13
Werner
Hallo Christian,
lad bitte mal eine Beispielmappe mit ein paar "Spieldaten" hoch. Die Tabellenblätter sollten aber in der Struktur deiner Arbeitsmappe entsprechen.
Bitte ohne Makros, kann derzeit nichts mit Makros herunterladen.
Gruß Werner
AW: Duplikate entfernen und Werte gleichzeitig sortier
18.03.2017 21:21:53
Christian
Hallo Werner und vielen Dank für deine Antwort. Ich musste eben kurz überlegen wie ich den nun auf deinen Beitrag antworten kann :) Das Forum ist noch ganz neu für mich.
https://www.herber.de/bbs/user/112275.xlsx
Ich weiss nicht ob du mit dem Link etwas anfangen kannst, meine Datei musste ich auf einen Server hochladen.
Anzeige
Rückfrage
18.03.2017 22:06:02
Werner
Hallo Christian,
aus deinem angangs geposteten Code bin ich davon ausgegangen, dass aus Blatt 1 Spalte H nach Blatt 2 Spalte C kopiert werden soll.
Jetzt sind die Daten aber plötzlich in Spalte B.
Also nochmal:
Blatt 1, Spalte B von B2 bis Bx kopieren nach Blatt 2, Ci
dann im Blatt 2 Duplikate entfernen
dann Blatt 2 Spalte C aufsteigend sortieren
Gruß Werner
Anzeige
AW: Rückfrage
18.03.2017 22:13:12
Christian
Ach ja genau, die Spaltenangaben sind aus meiner eigentlichen Excel Datei. Unabhängig von den Spalten bleibt die Zielstellung aber dieselbe.
Rüberkopieren, dabei die Duplikate entfernen, sortieren und dann noch wenn möglich, die Werte sozusagen als Spaltenüberschriften transpondieren. Aber das dürfte auch nur eine Zeilecode sein, weswegen ich das nicht gleich thematisiert habe mit den transpondieren. Wichtig ist mir jetzt erst mal zu verstehen warum da die Meldung "Objekt erforderlich rausgegeben wird"
Anzeige
AW: Rückfrage
18.03.2017 22:17:12
Werner
Hallo Christian,
und wohin transponieren, erste Zeile ab A1?
Zukünftig bitte nicht scheibchenweise.
Gruß Werner
AW: Rückfrage
18.03.2017 22:20:18
Christian
:) Ja das fällt mir auch gerade auf. Am besten ab C2. Ja, zukünftig Poste ich hier in einer klaren und strukturen Form :) Aber vielen lieben Dank für deine Bemühungen bis jetzt.
Anzeige
AW: Rückfrage
18.03.2017 22:52:14
Werner
Hallo Christian,
die Daten werden im Blatt 2 in der Spalte B "zwischengespeichert" und dort sortiert, dann nach C2 transponiert.
Das setzt natürlich voraus, dass im Spalte B vorher keine Daten sind, sonst würden diese überschrieben.
Public Sub Duplikate_raus()
Dim loLetzte As Long
Dim loLetzte1 As Long
Application.ScreenUpdating = False
With Sheets("tbl_UAE01")
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range(.Cells(2, 2), .Cells(loLetzte, 2)).Copy _
Sheets("tbl_test3").Range("B1")
End With
With Sheets("tbl_test3")
.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
loLetzte1 = .Cells(.Rows.Count, 2).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ActiveWorkbook.Worksheets("tbl_test3").Sort
.SetRange Range("B1:B" & loLetzte1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
With Sheets("tbl_test3")
.Range("B1:B" & loLetzte1).Copy
.Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
.Range("B1:B" & loLetzte1).ClearContents
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Gruß Werner
Anzeige
AW: Rückfrage
18.03.2017 23:22:11
Christian
Gibt es auch eine Möglichkeit die Werte in einen Art Array intern so aufzubereiten, das Sie in die zweite Tabelle gleich als Spaltenüberschriften hinzugefügt werden könnten. Also im Array würde das löschen der Duplikate und sortieren stattfinden.
Aber erst mal vielen lieben Dank für deine Bemühungen, den Ansatz werde ich gleich Morgen mal ausprobieren und weiter rum tüfflten :)
Anzeige
AW: Rückfrage
18.03.2017 23:41:45
Werner
Hallo Christian,
wozu?
Das Makro macht genau das, was du willst. Geschwindigkeit ist auch kein Problem. RemoveDuplicates und Sortieren sind sehr schnell und die Kopiervorgänge erfolgen im Block, daher auch unproblematisch. Einfach mal ausprobieren.
Hab jetzt aber noch eine kleine Erweiterung. Hier wird dann im Zielblatt die Formatierung aus Zelle C2 übernommen und in sämtliche Überschriften kopiert.
Public Sub Duplikate_raus()
Dim loLetzte As Long
Dim loLetzte1 As Long
Dim letzteSpalte As Long
Application.ScreenUpdating = False
With Sheets("tbl_UAE01")
loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range(.Cells(2, 2), .Cells(loLetzte, 2)).Copy _
Sheets("tbl_test3").Range("B1")
End With
With Sheets("tbl_test3")
.Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
loLetzte1 = .Cells(.Rows.Count, 2).End(xlUp).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ActiveWorkbook.Worksheets("tbl_test3").Sort
.SetRange Range("B1:B" & loLetzte1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
With Sheets("tbl_test3")
.Range("B1:B" & loLetzte1).Copy
.Range("C2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
letzteSpalte = .Cells(2, .Columns.Count).End(xlToLeft).Column
.Range("C2").Copy
.Range(.Cells(2, 4), .Cells(2, letzteSpalte)).PasteSpecial xlPasteFormats
.Range("B1:B" & loLetzte1).ClearContents
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Gruß Werner
Anzeige
AW: Rückfrage
19.03.2017 10:32:47
Christian
Werner... ich bin platt :D Das ist ja der pure Wahnsinn, was du da gezaubert hast. Boah vielen liebe Dank, daran sass ich die letzten Tage so oft vergebens.. Und hätte mich wohl auch einige Woche damit abgeqäult. Wahnsinn. Also vielen Dank ich suche jetzt gerade den Button zum Thread schließen. Meine Aufgabe ist mehr als erfüllt und ich bleibe diesen Forum jetzt auf jeden Fall treu und versuche auch selber meine Beiträge zu leisten. Ich will nicht nur nehmen sondern auch geben. Bei mir liegen 3 Bücher über VBA, ich denke mit der Zeit wird mein Niveau auch ein wenig höher liegen :)
Viele Grüße
Christian
Anzeige
Gerne u.Danke für die Rückmeldung.
19.03.2017 12:57:11
Werner
Hallo Christian,
...und für das Lernen in Sachen VBA kann ich dir nur empfehlen hier im Forum regelmäßig mitzulesen, zu versuchen einfache Problem aus dem Forum selbst zu lösen und zu schauen wie die anderen Helfer das angehen.
So habe/mache ich das.
Gruß Werner
Unerwartete Probleme
20.03.2017 16:18:40
Christian
Hallo Werner,
ich muss mich leider nochmal bei dir melden. Ich schaffe es einfach nicht die Formel an meine neuen Bedinungen anzupassen. Dieses mal soll die Spalte für den Sortier und Duplikate entfernen Vorgang A sein, also gleich die erste und die Werte ab B1 beginnen. Simpel dachte ich aber ich verstehe Teile vom Code nicht.

Public Sub Duplikate_Material()
Dim loLetzte As Long
Dim loLetzte1 As Long
Dim letzteSpalte As Long
Application.ScreenUpdating = False
With tbl_UAE01 'Codename (Indexname außerhalb des gültigen Bereichs)
loLetzte = .Cells(.Rows.Count, 1).End(xlUp).Row 'Anzahl der Zeilen in der ersten Spalte  _
der ersten Tabelle ermitteln
.Range(.Cells(2, 1), .Cells(loLetzte, 1)).Copy _
Sheets("tbl_MatrNr").Range("A1") 'Kopieren der Werte in Spalte 1 ab Zeile 2 und übertragen  _
in Zelle A1 in der zweiten Tabelle
End With
With tbl_MatrNr
'Zwischenspeicherung der Werte, Sortierung
'Spalte muss frei bleiben da ansonsten Werte überschrieben werden!!
.Range("A1:A").RemoveDuplicates Columns:=1, Header:=xlNo 'In der zwischenspalte A werden  _
die Duplikate entfernt
loLetzte1 = .Cells(.Rows.Count, 1).End(xlUp).Row 'Danach wird die neue Anzahl an Zeilen  _
ermittelt
.Sort.SortFields.Clear 'Vorsortierung löschen
.Sort.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Werte in der  _
zwischenspalte A sortieren
End With
With tbl_MatrNr.Sort '
.SetRange Range("A1:A" & loLetzte1) 'Sortieren mit der neuen Anzahl an Zeilen
.Header = xlNo 'Keine Headerformatierung
.MatchCase = False '?
.Orientation = xlTopToBottom 'Text orientierung
.Apply 'Sortierung anwenden
End With
With tbl_MatrNr
.Range("A1:A" & loLetzte1).Copy 'Kopieren mit neuen Bereich
.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True 'Einfügen der bearbeitenden Werte ab B1 in transponieren Form
letzteSpalte = .Cells(1, .Columns.Count).End(xlToLeft).Column 'Ermittlung der letzen  _
Spalte (Zeile 1, Ermittlung Spalte)
.Range("B1").Copy
.Range(.Cells(1, 2), .Cells(1, letzteSpalte)).PasteSpecial xlPasteFormats
.Range("A1:A" & loLetzte1).ClearContents
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Zum besseren Verständnis habe ich den Code so gut es geht kommentiert. Spätestens ab den letzten Block verstehe ich nicht ganz die Vorgehensweise
https://www.herber.de/bbs/user/112302.xlsx
Eine Beispiel Tabelle: A1 soll zum Zwischenspeichern des Codes sein und ab B1 die eindeutigen Werte aus der ersten Spalte in der tbl_UAE01.
Vielen lieben Dank für eure Unterstützung
Viele Grüße
Christian
Anzeige
AW: Unerwartete Probleme
20.03.2017 18:56:43
Christian
Danke Werner du bist echt ein Held, ich konnte den Code jetzt leicht auf die neuen Bedinungen anpassen. Die VBA Welt ist so bunt und groß und es macht spaß wenn Dinge dann auf Einmal laufen :)
Viele Grüße
Christian
Anzeige
Gerne und weiterhin happy Exceling. o.w.T.
20.03.2017 18:57:52
Werner
AW: Duplikate entfernen und Werte gleichzeitig sortier
18.03.2017 22:28:25
Ur-Opa
Hallo Christian,
das Makro hat sich wohl an der Verschachtelung der beiden With.. Statments verschluckt.
Nachfolgend eine Quick-and-Dirty-Lösung.
Sub DuplikateEntfernen()
'    Dim Zeile As Long
'    Dim Zeilemax As Long
'    With tbl_UAE01
'      Zeilemax = .UsedRange.Rows.Count
'        For Zeile = 2 To 800
ThisWorkbook.Worksheets("tbl_UAE01").Range("H2:H800").Copy _
Destination:=Worksheets("tbl_test3").Range("C1")
'        Next Zeile
'    End With
Worksheets("tbl_test3").Range("C1:C800").RemoveDuplicates _
Columns:=1, _
Header:=xlNo
With ThisWorkbook.Worksheets("tbl_test3").Sort
.SetRange Range("C1:C800")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Check bitte mal, ob es so richtig läuft.
Viel Erfolg
Ur-Opa
Anzeige
AW: Duplikate entfernen und Werte gleichzeitig sortier
18.03.2017 22:35:57
Christian
Ach so, die Verschachtelung könnte natürlich auch ein Problem sein, wobei ich dachte die wäre wichtig, da ich ja in zwei verschiedenen Tabellen argiere und somit ja zwei "with" Befehle benötige.
@ur-opa bei dir kommt jetzt die Meldung "Index außerhalb des gültigen Bereichs" bei der Zeile
ThisWorkbook.Worksheets("tbl_UAE01").Range("H2:H800").Copy _
Destination:=Worksheets("tbl_test3").Range("C1")
Anzeige
AW: Duplikate entfernen und Werte gleichzeitig sortier
19.03.2017 00:12:31
Ur-Opa
Hallo Christian,
Pruefe bitte mal, ob die Worksheet-Namen in deiner aktuellen Test-Datei korrekt im Coding abgebildet sind.
Ausserdem kann "ThisWorkbook." entfernt werden.
Gruss
Ur-Opa
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Duplikate entfernen und Werte gleichzeitig sortieren in Excel


Schritt-für-Schritt-Anleitung

Um Duplikate in Excel zu entfernen und gleichzeitig die Werte zu sortieren, kannst Du den folgenden VBA-Code verwenden. Dieser Code kopiert die Daten aus einer Tabelle, entfernt Duplikate, sortiert die Werte und fügt sie in eine zweite Tabelle ein.

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Füge ein neues Modul ein (Einfügen > Modul).
  3. Kopiere den folgenden Code in das Modul:
Public Sub DuplikateEntfernen()
    Dim loLetzte As Long
    Dim loLetzte1 As Long
    Application.ScreenUpdating = False

    ' Daten kopieren
    With Sheets("tbl_UAE01")
        loLetzte = .Cells(.Rows.Count, 2).End(xlUp).Row
        .Range(.Cells(2, 2), .Cells(loLetzte, 2)).Copy _
        Sheets("tbl_test3").Range("B1")
    End With

    ' Duplikate entfernen
    With Sheets("tbl_test3")
        .Range("B:B").RemoveDuplicates Columns:=1, Header:=xlNo
        loLetzte1 = .Cells(.Rows.Count, 2).End(xlUp).Row

        ' Sortierung durchführen
        .Range("B1:B" & loLetzte1).Sort Key1:=.Range("B1"), Order1:=xlAscending, Header:=xlNo
    End With

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
  1. Schließe den VBA-Editor und kehre zu Excel zurück.
  2. Führe das Makro aus (drücke ALT + F8, wähle DuplikateEntfernen und klicke auf Ausführen).

Häufige Fehler und Lösungen

  • Fehlermeldung: "Objekt erforderlich"

    • Diese Fehlermeldung tritt häufig auf, wenn die Referenz auf das Arbeitsblatt nicht korrekt ist. Achte darauf, dass die Blattnamen in Deinem Code mit den tatsächlichen Namen in Deiner Arbeitsmappe übereinstimmen.
  • Duplikate werden nicht entfernt

    • Stelle sicher, dass die Spalte, aus der Du die Duplikate entfernen möchtest, tatsächlich Werte enthält und dass Du die richtige Spalte im Code angegeben hast.
  • Sortierung funktioniert nicht

    • Überprüfe, ob die Daten in der Zieltabelle korrekt kopiert wurden. Die Sortierung funktioniert nur, wenn die Zellen nicht leer sind.

Alternative Methoden

Falls Du keine VBA-Lösungen nutzen möchtest, kannst Du auch die integrierte Funktion in Excel verwenden:

  1. Markiere die Spalte, in der Du Duplikate entfernen möchtest.
  2. Gehe zu Daten > Duplikate entfernen.
  3. Wähle die Spalte aus und klicke auf OK.
  4. Zum Sortieren der Werte kannst Du die Spalte markieren und auf Daten > Sortieren klicken.

Diese Methode ist einfach und benötigt keine Programmierkenntnisse.


Praktische Beispiele

Beispiel 1: Duplikate in einer Liste entfernen und sortieren

Angenommen, Du hast eine Liste von Produkten in tbl_UAE01, Spalte B:

B
Apfel
Banane
Apfel
Orange
Banane

Nach Ausführung des Makros wird die Liste in tbl_test3 so aussehen:

B
Apfel
Banane
Orange

Die Liste ist nun duplikatfrei und kann nach Belieben sortiert werden.


Tipps für Profis

  • Array-Methoden nutzen: Du kannst auch Arrays verwenden, um die Leistung zu verbessern, wenn Du mit großen Datenmengen arbeitest. Das Entfernen von Duplikaten und das Sortieren kann im Array schneller erfolgen.

  • Fehlerbehandlung einbauen: Überlege, Fehlerbehandlungsroutinen in Deinen VBA-Code einzubauen, um unerwartete Fehler abzufangen.

  • Benutzerdefinierte Formate: Wenn Du die Werte transponieren möchtest, achte darauf, die Formatierung korrekt zu kopieren.


FAQ: Häufige Fragen

1. Wie entferne ich Duplikate in Excel ohne VBA? Du kannst die Funktion Duplikate entfernen unter dem Tab Daten nutzen.

2. Was mache ich, wenn die Sortierung nicht funktioniert? Überprüfe die Daten auf leere Zellen und stelle sicher, dass die Werte korrekt kopiert wurden.

3. Kann ich mehrere Spalten auf einmal auf Duplikate prüfen? Ja, beim Duplikate entfernen kannst Du mehrere Spalten auswählen, um eine umfassendere Überprüfung durchzuführen.

Beliebteste Forumthreads (12 Monate)

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