Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1080to1084
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
Inhaltsverzeichnis

Spaltenvergleich

Spaltenvergleich
11.06.2009 17:03:02
Sandra
Hallo zusammen,
ich muss ca,. 50.000 Datensätze prüfen, ob sie doppelt vorkommen. Dafür nutze ich folgendes Makro:

Sub Spaltenvergleich_mit_Farbe()
'Vergleicht die Spalte A des Tabellenblattes 1 mit der Spalte 1 des Tabelenblattes 2 und  _
markiert die doppelten Werte in rot
Application.Calculation = xlManual
Application.ScreenUpdating = False
Dim i As Integer, j As Integer
'Festlegen der Variabeln
EndeA = Worksheets("1").Cells(Rows.Count, 1).End(xlUp).Row
'legt das Ende des ersten Bereiches fest
EndeB = Worksheets("2").Cells(Rows.Count, 1).End(xlUp).Row
'legt das Ende des zweiten Bereiches fest
For i = 1 To EndeA
'beginnt bei Zelle 1 und hört bei der letzten ausgefüllten Zelle des Bereiches auf
For j = 1 To EndeB
If Sheets("1").Cells(i, 1) = Sheets("2").Cells(j, 1) Then _
Sheets("2").Cells(j, 1).Interior.ColorIndex = 3
'Fortsetzen der Schleife
Next j
Next i
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub


Nun hätte ich drei Bitten:
a) kann man das Makro in der Schnelligkeit noch optimieren? Wenn ja, wie?
b) kann man die StatusBar einbauen, so dass nach 1000 Datensätzen die entsprechende Anzahl
angezeigt wird z.B. Zeile 1000). Wäre mir wichtig, damit ich weiß da passiert noch was.
c) Kann man auch die automatische Speicherung nach 1000 Datensätzen einbauen?
Mir ist die Datei jetzt schon mehrfach abgestürzt bzw. es hat sich nichts mehr getan und ich habe dann abgebrochen.
Problem: Ich bräuchte genaue Vorschläge, weil mein VBA-LEvel bescheidener als bescheiden ist. :-)
Vielen Dank für Eure Hilfe!
Liebe Grüße
Sandra

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Spaltenvergleich
11.06.2009 17:56:00
Roland
Hallo Sandra,
schaufel die beiden zu vergleichenden Spalten in Arrays, fahre diese gegeneinander, merke die Übereinstimmungen in einer Variable und färbe gleichzeitig das 2. Blatt an passender Stelle ein, dann geht es deutlich schneller. Der folgende Code macht dies; probiere ihn aus und gib bitte eine Rückmeldung:

Sub DoppelteKennzeichnen()
Dim arr1, arr2, i As Long, j As Long, k As Long, EndeA As Long, EndeB As Long
EndeA = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
EndeB = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row
With ActiveWorkbook.Worksheets(1)
arr1 = .Range(.Cells(1, 1), .Cells(EndeA, 1))
End With
With ActiveWorkbook.Worksheets(2)
arr2 = .Range(.Cells(1, 1), .Cells(EndeB, 1))
.Range(.Cells(1, 1), .Cells(EndeB, 1)).Interior.ColorIndex = xlNone
End With
For i = LBound(arr1) To UBound(arr1)
If i Mod 1000 = 0 Then Application.StatusBar = i & " Datensätze wurden verglichen"
For j = LBound(arr2) To UBound(arr2)
If Trim$(arr1(i, 1)) = Trim$(arr2(j, 1)) Then
ActiveWorkbook.Worksheets(2).Cells(j, 1).Interior.ColorIndex = 3
k = k + 1
Exit For
End If
Next
Next
MsgBox k & " doppelte Datensätze wurden gekennzeichnet", 64, "Fertig"
Erase arr1
Erase arr2
Set arr1 = Nothing
Set arr2 = Nothing
Application.StatusBar = False
End Sub


Gruß von
Roland Hochhäuser

Anzeige
AW: Spaltenvergleich
11.06.2009 18:14:28
Sandra
Hallo Roland,
das Makro ist genial. HAbe das gerade mal mit 15.000 Datensätzen ausprobiert und es geht super schnell - ohne Absturz. Einfach genial!
Ein riesen "Dankeschön" an Dich. Das ist einfach toll. Vielen Dank!
Eine Bitte hätte ich noch: Könntest Du noch einbauen, dass nach jeweils 1000 Datensätzen auch gespeichert wird.
Vielen Dank!
Liebe Grüße
Sandra
@ Roland: BRauche noch einmal
11.06.2009 21:09:38
Sandra
Deine Hilfe (siehe Beitrag zuvor). Hatte offen vergessen.
AW: @ Roland: BRauche noch einmal
11.06.2009 23:47:21
Roland
Hallo Sandra,
erst mal Danke für die Blumen ;-)
Zu deiner Nachfrage: wozu überhaupt *vorsichtig nachfrag*, das Makro stürzt definitiv auch bei mehreren 100000 Datensätzen (getestet mit Excel 2007) nicht ab und der ganze Speed ginge mächtig in den Keller?!
Und wenn doch, wie genau? Willst Du, dass nach jeweils 1000 Datensätzen die Mappe an sich gespeichert wird (wegen fehlender Absturzgefahr unnötig, siehe oben) oder eine Kopie der Mappe, z.B. mit dem Zusatz Dateiname1000.xls, Dateiname2000.xls usw., damit du die Zwischenstände checken kannst? Dann hättest Du bei 50.000 zu vergleichenden Datensätzen allerdings mindestens 50 neue Excelmappen *am Kopf kratz*
Ebenfalls Lieben Gruß aus Berlin von
Roland Hochhäuser
Anzeige
@ Roland: Hast recht.
13.06.2009 14:46:43
Sabine
Danke für die Tipps.
AW: Spaltenvergleich
11.06.2009 17:56:45
Firmus
Hallo Sandra,
ohne VBA?
Die Funktion "=COUNTIF(G:G;G2)" (deutsch Zählenwenn) prüft wie oft der Wert der Zelle G2 in der Spalte G vorkommt.
Diese Funktion ist in eine freie spalte in diesem Beispiel in H2 einzutragen und nach unten zu kopieren.
Über Filter kannst Du die Duplikate sehen. (wert größer 1 in Spalte H).
mit VBA:
schreib doch einfach bei jeden Schleifendurchlauf in eine sichtbare Zelle (ich nehme meist A1)
die index-variable. Damit siehts Du ob "es noch tut".
mit Application.ScreenUpdating = False oder True kannst Du die Anwendung beschleunigen.
Damit ist aber die Änderung in A1 nicht mehr sichtbar. Deshalb sollte False/True immer bei 1000 mit
geschaltet werden. Damit ist das Macro - mit einfachen Mitteln und ohne viel overhead zu betreiben.
Hoffe das hilft Dir weiter,
Gruß aus Bayerisch-Schwaben,
Firmus
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige