Hallo Wissende…
eine Excel-Arbeitsmappe mit mehreren gleich aufgebauten Arbeitsblättern soll automatisch auf mehrfache Einträge durchsucht werden. Vergleichsrelevant ist in jedem Blatt jeweils nur eine der Spalten (im Format „Standard“).
Nun soll Excel in jedem Arbeitsblatt diejenigen Zeilen markieren (aber
nicht wegfiltern), in denen ein Eintrag steht, der in der gesamten xls
doppelt oder mehrfach vorkommt.
- Frage: Geht das überhaupt?
- Frage: Falls ja, welchen Ansatz müsste ich dazu verfolgen?
- Frage: Liegt so ein Duplikat-Checker vielleicht sogar irgendwo schon fix und fertig rum?
Mein Problem dabei ist, dass ich noch nie mit Virtual Basic und Variablen gearbeitet habe. Und im Handbuch finde ich keinen greifbaren Ansatz dafür.
Lösung mit Bedingter Formatierunge
Format -> Bedingte Formatierung
Formel ist -> =ZÄHLENWENN($A$1:blush:N$500;INDIREKT(ADRESSE(ZEILE();SPALTE())))>1
wobei $A$1:blush:N$500 der Bereich ist, innerhalb dem Du nach duplikaten suchen willst.
Ich empfehle das lieber nicht zuuuuuuuuuu hoch zu setzen weil bei jeder änderung im Sheet neu kalkuliert werden muss und das das ganze arg langsam machen kann…
Grüße
Munich
Erst mal vielen Dank,
@Munich:
kriege ´nen Zirkelbezug, wenn ich´s probiere. Unklar ist auch der
Umgang mit den Platzhaltern (ADRESSE, ZEILE, SPALTE), falls es
welche sind.
@Xen55
deine postings setzen voraus, dass sich die zu vergleichenden Spalten auf nur einem einzelnen Blatt befinden. Ich habe aber mehrere mit je einer Ziel-Spalte für den Abgleich.
Bin dennoch mal nach „Bauplan“ vorgegangen. Funzt aber nicht.
Zur Veranschaulichung ist jetzt mal eine dummy.xls downloadbar.
http://www.fallnich.net/probs/dummy.xls
Vielleicht könnt ihr ja noch mehr dazu sagen.
best
Siggi Lang
Hallo Sigurd,
hier ein Vorschlag zur Lösung mit VBA. In den ersten Zeilen des Makros muß du noch die Zeilen, Spalten einstellen, die für deine Arbeitsmappe zutreffen sollen.
Das Makro durchläuft in 2 Schleifen Zeile für Zeile die Spalte in allen Tabellen und markiert Mehrfacheinträge. In einer Hilfsspalte wird „M“ eingetragen, um in den Schleifen bereits geprüfte Zellen zu überspringen. Diese „M“ können zum Schluß wieder gelöscht werden.
Kommt ein Eintrag mehrfach vor, so wird auch die Zeile markiert, in der der Eintrag das 1. mal vorkommt. Diesen Codeteil kannst du bei Bedarf weglassen.
Gruß
Franz
Sub Tabellenvergleich()
' Durchsucht eine Spalte in allen Tabellen der Mappe nach Mehrfacheinträgen
Spalte = 1 'Spalte die durchsucht werden soll
SpalteM = 6 'Spalte für Markierung der Mehrfacheinträge ("M" wird eingetragen)
Zeile1 = 1 'Zeile in der in jeder Tabelle mit dem Vergleich begonnen werden soll
Farbe = 3 ' Colorindex für Füllfarbe bei Mehrfacheinträgen, 3 = Rot
With ActiveWorkbook
For i = 1 To .Sheets.Count
For ZeileI = Zeile1 To .Sheets(i).Cells(.Sheets(i).Rows.Count, Spalte).End(xlUp).Row
'Bereits markierte Zeile überspringen
If .Sheets(i).Cells(ZeileI, SpalteM) = "M" Then GoTo NextI
Wert = .Sheets(i).Cells(ZeileI, Spalte)
'Vergleich mit restlichen Zellen
For j = i To .Sheets.Count
'Startzeile für Vergleichstabelle setzen
If i = j Then
' steht Wert in der letzten Zeile des Blattes i dann wird im nächten Blatt weitergesucht
If ZeileI = .Sheets(i).Cells(.Sheets(i).Rows.Count, Spalte).End(xlUp).Row Then Exit For
ZeileJStart = ZeileI + 1
Else
ZeileJStart = Zeile1
End If
For ZeileJ = ZeileJStart To .Sheets(j).Cells(.Sheets(j).Rows.Count, Spalte).End(xlUp).Row
'Prüfung ob Zeile bereits markiert als Mehrfacheintrag
If .Sheets(j).Cells(ZeileJ, SpalteM) = "M" Then GoTo NextJ
'Wertevergleich
If Wert = .Sheets(j).Cells(ZeileJ, Spalte) Then
.Sheets(j).Cells(ZeileJ, SpalteM) = "M"
.Sheets(j).Rows(ZeileJ).Interior.ColorIndex = Farbe
Mehrfach = True
End If
NextJ:
Next ZeileJ
Next j
'1. Zeile mit Wert auch markieren
If Mehrfach = True Then
.Sheets(i).Cells(ZeileI, SpalteM) = "M"
.Sheets(i).Rows(ZeileI).Interior.ColorIndex = Farbe
Mehrfach = False
End If
NextI:
Next ZeileI
Next i
End With
' Markierungen in SpalteM entfernen?
If MsgBox("Markierung 'M' in Spalte " & SpalteM & " entfernen?", vbYesNo + vbQuestion, \_
"Mehrfacheinträge suchen") = vbYes Then
ActiveWorkbook.Sheets.Select
Columns(SpalteM).Select
Selection.ClearContents
Range("A1").Select
Sheets(1).Select
End If
End Sub
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
Hi Franz!
Wow! Das Script macht so genau, was ich brauche.
Nach einigem Hin und Her ist das jetzt eingebaut.
Jetzt fehlt mir nur noch der Farbwert für „hellgelb“ (komme nicht hinter das Zahlensystem…) und eine weitere Bedingung, nämlich dass ein zu vergleichendes Feld überhaupt einen Inhalt hat.
if (Feld leer)=false (oder wie das geht…)
thanxalot!
Siggi
Hallo Siggi,
leider kann man an Hand der Mustertabelle unter Extras-Optionen wo man die Farben einstellen kann nicht auf die Index-Nr. schließen
Hell-Gelb hat wenn ich nicht farbenblind bin die Nr. 36
Folgendes Makro erstellt dir in einer Tabelle ein Farbmuster mit den 56 Index-Nrn., wobei sich einige Farben wiederholen, da einige Nummern speziell für die Darstellung in Diagrammen verwendet werden.
Sub Farben()
J = 1
Zeile = 2
For I = 1 To 56
If I = 21 Or I = 41 Then J = J + 3: Zeile = 2
Cells(Zeile, J).Interior.ColorIndex = I
Cells(Zeile, J + 1).Value = I
Zeile = Zeile + 1
Next I
End Sub
Leere Zellen kann man z.B. wie folgt prüfen:
If IsEmpty(.Sheets(i).Cells(ZeileI, Spalte)) = True Then
Gruß
Franz
[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]
@Franz
Sauber. Das Script ist jetzt nach deinen Tipps modifiziert und funzt.
Ich lasse die Beispieldatei für die Nachwelt mal so im Web stehen.
http://www.fallnich.net/probs/dummy.xls
Problem gelöst.
Dank an alle.
Siggi