Microsoft Excel

Herbers Excel/VBA-Archiv

Datensätze auf bekannte Werte prüfen

Betrifft: Datensätze auf bekannte Werte prüfen von: Sven
Geschrieben am: 30.10.2020 12:01:10

Hallo Leute,
ich habe eine monatliche Liste von 2000-3000 Datensätzen, die ich neu importiere. Ab und zu kann es sein, dass dort ein durch die Artikelnummer identifizierbarer Datensatz enthalten ist, den ich schon in einem Vormonat dabei hatte. Ich würde diesen gerne markieren um ihn von der Bearbeitung ausschließen.
Wenn ich hierfür also eine "Blacklist" pflege, in die ich jeden Monat die Artikelnummern schreibe und die am Jahresende um die 30000 Zeilen groß ist, ist es arg unperformant, wenn ich jeden neuen Import mittels Schleifendurchlauf abprüfe.
Gibt es da eine andere colle Lösung?

Grüße
Sven

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: JoWE
Geschrieben am: 30.10.2020 12:25:54

Hallo Sven,

sieh mal hier:
https://www.heise.de/tipps-tricks/Excel-Duplikate-entfernen-so-werden-Sie-doppelte-Werte-los-4316037.html

Gruß
Jochen

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: Sven
Geschrieben am: 30.10.2020 12:45:17

Hallo Jochen,
danke. Das hilft aber nicht weiter. Hier wird ja nur innerhalb des jeweiligen Imports auf Duplikate geprüft. Die wird es nicht geben. Der Vergleich müsste ja mit einer anderen Liste erfolgen.

Betrifft: AW: war mit anderem Ansatz gedacht...
von: JoWE
Geschrieben am: 30.10.2020 14:00:57

stimmt, die Idee war anders::
- alles neue unter den vorhandenen Werten einfügen
- und danach den Abgleich machen.
Geht schnell und ohne Programmierung.

Gruß
Jochen

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: volti
Geschrieben am: 30.10.2020 12:48:42

Hallo Sven,

leider weiß ich nicht, wie Du Deine Schleife gestaltest hattest und wo bei Dir unperformant anfängt.

Wenn Dir Jochen's Lösung nicht zusagen sollte, probiere mal folgende Anregung aus. Das ist zwar auch eine Schleife, aber nur über die importierten Daten. 2000 Datensätze sollten nicht so lange dauern.

Code:
[Cc]

Sub MarkiereArtikel() Dim iZeile As Long With ActiveSheet For iZeile = 1 To .Cells(Rows.Count, "A").End(xlUp).Row If WorksheetFunction.CountIf(Worksheets("Blacklist").Range("A:A"), _ .Cells(iZeile, "A").Value) > 0 Then .Cells(iZeile, "A").Interior.Color = RGB(255, 255, 0) End If Next iZeile End With End Sub

_________________________
viele Grüße aus Freigericht 😊
Karl-Heinz


Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: Sven
Geschrieben am: 30.10.2020 13:24:53

Hallo Karl-Heinz,
oh, danke für den Code.
Allerdings mache ich offenbar irgendetwas falsch:
With ThisWorkbook.Worksheets("DWH")
            For lonZeilen = 1 To .Cells(Rows.Count, "G").End(xlUp).Row
                If WorksheetFunction.CountIf(Worksheets("Blacklist").Range("A:A"), _
                    .Cells(lonZeilen, "G").Value) > 0 Then
                    .Cells(lonZeilen, "G").Interior.Color = RGB(255, 255, 0)
                End If
            Next lonZeilen
        End With

DWH ist das neu importierte Tabellenblatt mit besagter Nummer in Spalte G.
Die Schleife wird durchlaufen, markiert wird aber nichts.

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: volti
Geschrieben am: 30.10.2020 13:47:59

Hallo Sven,

Dein Code sieht mir eigentlich korrekt aus. Ich habe ihn nachgebaut und es funktioniert
Und die Referenzartikelnummern stehen bei Dir auch in Spalte $A?

Gibt es ggf. Unterschiede bzgl. des Formats?
Ansonsten müsstest Du mal eine abgespeckte Probemappe hochladen

viele Grüße
Karl-Heinz

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: volti
Geschrieben am: 30.10.2020 13:53:27

Nachtrag:

Es wird auch nichts markiert, sondern zunächst nur gelb hinterlegt.
VG KH

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: Sven
Geschrieben am: 30.10.2020 14:16:50

Schön, beim Anonymisieren der Arbeitsmappe hatte es dann geklappt. Ich hatte wohl wirklich Formatunterschiede. Vielen Dank.

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: Sven
Geschrieben am: 30.10.2020 14:20:46

Achso, wie würdest Du die nun geprüften IDs der Blacklist anfügen, damit sie nächsten Monat berücksichtigt werden?

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: volti
Geschrieben am: 30.10.2020 15:04:28

Hallo Sven,

hier eine Idee:

Code:
[Cc][+][-]

Sub MarkiereArtikel() Dim lonZeilen As Long Dim sNeueArtikel As String, sArr() As String Dim WSh As Worksheet Set WSh = Worksheets("Blacklist") With ThisWorkbook.Worksheets("DWH") For lonZeilen = 1 To .Cells(Rows.Count, "G").End(xlUp).Row If WorksheetFunction.CountIf(WSh.Range("A:A"), _ .Cells(lonZeilen, "G").Value) > 0 Then .Cells(lonZeilen, "G").Interior.Color = RGB(255, 255, 0) Else sNeueArtikel = sNeueArtikel & .Cells(lonZeilen, "G").Value & "," End If Next lonZeilen End With 'Neue Artikel aufnehmen If sNeueArtikel <> "" Then sArr = Split(sNeueArtikel, ",") 'Neue Artikelnummern in Array lonZeilen = WSh.Cells(Rows.Count, "A").End(xlUp).Row + 1 WSh.Cells(lonZeilen, "A").Resize(UBound(sArr), 1).Value = Application.Transpose(sArr) End If End Sub

_________________________
viele Grüße aus Freigericht 😊
Karl-Heinz


Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: Sven
Geschrieben am: 02.11.2020 09:52:11

Klasse, Karl-Heinz! Das klappt.
Selbst wenn 1000 Nummern auf die schwarze Liste gesetzt werden funktioniert es - ich hätte befürchtet, dass der String da irgendwann vollläuft...

Grüße
Sven

Betrifft: AW: Datensätze auf bekannte Werte prüfen
von: volti
Geschrieben am: 02.11.2020 10:14:47

Hallo Sven,

danke für die positive Rückmeldung.

Der String läuft nicht voll, bzw. das hängt vom verfügbaren Memory ab. 😊

Als ich anfing zu programmieren, durfte ein String max. 255 Zeichen haben, später war's dann ein Segment, also 65.535 Zeichen.....

viele Grüße
Karl-Heinz

Beiträge aus dem Excel-Forum zum Thema "Datensätze auf bekannte Werte prüfen "