Auswertung von zigtausend Daten über Array

[Gast 123]

Hallo Kollegen
hallo Uwe

@Uwe  ich habe im Thread über Text zerlegen deine wunderschöne kurze Array Lösung gesehen, aber nicht verstanden.  
Mit Arrays habe ich mich nie beschaeftigt, weil mir ihre korrekte Deklaration und Dimensionişerung ein grosses Raetsel ist. Ich sehel: Dim varText(1 To 4, 1 To 1) habe aber nicht verstanden warum das so dimensioniert wird?? Lasst man "1 to 1" weg wird nur eine Zeile ausgegeben. Was macht dieses 1 to 1?

Offene Frage:
ein Programm von mir braucht 45 Minuten für Datenvergleich über For Next Schleifen. Das ist sehr lang. Geht das über Arrays schneller?? Mein Problem: Dde Quelle hat über 30 Spalten, im Ziel sind es 10. Die Spalten sind nicht nebeneinander, stark verstreut. Wobei jede einzelne Zelle geprüft werden muss. Bei der Array Lösung von Uwe kam mir die Idee ob ein Vergleich schneller geht wenn man z.B. 1000 Daten in ein Array laedt und sie dort vergleicht statt in den Zellen. Mir fehlt aber jedes Wissen und die Erfahrung wie man so ein Array Dimensionieren und danach vergleichen muss? 

Habt ihr dazu bitte einen brauchbaren Vorschlag.  Vielen Dank für eure Mithilfe.

mfg Gast 123

[snb]

Ola Gast 123

Wartete schon sehr lange auf dich:  https://www.snb-vba.eu/VBA_Arrays_en.html

Mit Array kann deine Datei blitzschnell werden.
Eine Musterdatei wäre hilfreich.

[Gast 123]

Hallo snb

ich freue mich sehr von dir eine Antwort zu bekommen. Mein Problem zum Link:  ich kann KEIN Englisch!!  Die VBA Befehle erlernt man irgenwann mal ....

Anbei zwei Beispieldateien als xlsx die ich an anderer Stelle bearbeitet habe, aber es bleibt das Zeit Problem von ca. 45 Minuten. Wie du ersehen kannst waren die ersten Angaben sehr wage. Deshalb drei Lösungsversuche von mir. Meine Array Version hat einen Laufzeitfehler, den ich mit Resume Next abfangen musste. War das Array falsch deklariert??

Die Spalten C-G kann man als zusammenhaengenden Block ins Array laden und vergleichen. Ab Spalte H in der Zieltabelle kommen Einzelspalten in der Quelldatei, ausser den beiden letzten Spalten AY+AZ. Bei den Zeilen gibt es KEINE 1:1 Übereinstimmung, weshalb ich neue Daten in der Zieldatei unten angehangen habe. Das macht den Vergleich schwierig. 

Ich bin überzeugt das du mit deinem Wissen und deiner Erfahrung eine bessere Lösung hast. Der Frager gab zurück das die beiden ersten Makro Versionen einwandfrei laufen, aber leider zuviel Zeit benötigen. Die Choose Version ist noch ungetestet.  Ich denke das eine Array Lösung hier eindeutig schneller sein wird, kann es aber leider nicht programmieren.

mfg Gast 123

[Ego]

Hallo Gast123,

nach deinen Beiträgen hier ,die ja oft von dir erstellte Programme beinhalten, nehme an, dass dir die Nutzung von arrays in Programmiersprachen bekannt ist.

Für deine speziellen Fragen zur Schnittstelle zwischen dem array in VBA und Bereichen in Arbeitsblätter sind meines Erachtens zwei Abschnitte  aus snb's Seite wichtig.

A) Warum benötigt man auch für einen Zeilen- oder Spaltenbereich ein zweidimensionales array

5.4.1.2 1-dimensional array
Every 'Range' in an Excel worksheet has a row and a column dimension.
Also 1 row only or 1 column only is a multi dimensional array.

Jeder Bereich in einem Excelarbeitsblatt hat eine Zeilen- und eine Spaltendimension.
Wenn ich also einen Bereich in eine Variant-Variable einlese, erhalte ich immer ein zweidimensionales Array. Die erste Dimension ist die Zeile (1 to AnzZeilen), die zweite die Spalte(1 to AnzSpalten). Wenn der Bereich nur eine Spalte hat, ist das array trotzdem zweidimensional mit der zweiten Dimension 1 to 1.
Umgekehrt muss ich, wenn ich ein Array in einem Bereich ausgeben will, ein zweidimensionales array nutzen.

B) Zu

...Geht das über Arrays schneller?? ... Die Spalten sind nicht nebeneinander

Ja.
Wenn man die Daten in ein array einläd und dann im Speicher bearbeitet ist ein Geschwindigkeitsfaktor grösser als 100 nach meinen Erfahrungen keine Seltenheit.
Bei nur dreißig Spalten (und wahrscheinlich weniger als 100.000 Zeilen) kann man, den gesamten Bereich einlesen. (Die genauen Speichergrenzen kenne ich nicht.)
In 6.7 hat snb aber auch angegeben, wie man nur vorgegebene Spalten in gewünschter Reihenfolge in ein Array einliest:

6.7.1 Excel function Index
a_sp=application.index(range("A1:H10"),[row(1:10)],array(5,3,1))

[snb]

Ich hab's so gemacht:

Code:

Sub M_snb()
   sn = Workbooks("Quelle Tabellen vergleichen.xlsx").Sheets("Tabelle1").Cells(1).CurrentRegion
   sp = Workbooks("Ziel Tabellen vergleichen.xlsx").Sheets("Tabelle1").Cells(1).CurrentRegion
   
   With CreateObject("scripting.dictionary")
        For j = 2 To UBound(sn)
            .Item(Format(sn(j, 1))) = Application.Index(sn, j)
        Next
   
        For j = 2 To UBound(sp)
            If .exists(Format(sp(j, 1))) Then
               sq = .Item(Format(sp(j, 1)))
               For jj = 3 To UBound(sp, 2)
                  If sp(j, jj) <> sq(Val(Right(sp(1, jj), 2))) Then sp(j, jj) = sp(j, jj) & "_"
               Next
             End If
        Next
    End With
    
    With Workbooks("Ziel Tabellen vergleichen.xlsx").Sheets("Tabelle1").Cells(1).CurrentRegion.Offset(24)
      .Value = sp
      With .FormatConditions.Add(2, , "=RIGHT(A25;1)=""_""").Interior
            .PatternColorIndex = -4105
            .Color = 49407
            .TintAndShade = 0
        End With
    End With
End Sub

[snb]

Dieser Code ist schneller:
Das geöffnete Workbook(Thisworkbook) ist 'Ziel Tabellen vergleichen.xlsb'

PHP-Code:

Sub M_snb()
  sp = ThisWorkbook.Sheets("Tabelle1").Cells(1).CurrentRegion
    
  With CreateObject("ADODB.Recordset")
    .Open "SELECT * FROM `Sheet1$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=G:\OF\Quelle Tabellen vergleichen.xlsx;Extended Properties=""Excel 12.0 Xml"""
    For j = 2 To UBound(sp)
      .Filter = .Fields(0).Name & "='" & sp(j, 1) & "'"
      For jj = 3 To UBound(sp, 2)
        If sp(j, jj) <> .Fields(sp(1, jj)) Then sp(j, jj) = sp(j, jj) & "_"
      Next
    Next
  End With

  With ThisWorkbook.Sheets.Add(, Sheets(Sheets.Count)).Cells(1).Resize(UBound(sp), UBound(sp, 2))
    .Value = sp
    If .FormatConditions.Count = 0 Then .FormatConditions.Add(2, , "=RIGHT(A1;1)=""_""").Interior.Color = 49407
  End With
End Sub 


[Gast 123]

Hallo an alle Kollegen

@Ego  vielen Dank für die Erklaerung Zeilen, Spalten beim Array. Habe ich bisher nicht gewusst! Das ganze Array Thema ist mir noch ein Buch mit 7 Siegeln. Sieht man auch an meinen Programmen, ich verwende Arrat sehr selten. Dann ist es von anderen Kollegen "kopiert". Stehe offen dazu!!

@snb  herzlichen Dank für deine freundliche Hilfe, ich hoffe damit kann man die Aufgabe richtig lösen. Im Beispiel habe ich den Code ein wenig geaendert, um ihn zu verstehen, bekomme aber die Farbmarkierung nicht ans laufen. Durch Versuch fand ich heraus das man den Wert aus der Quelldatei in der zweiten For Next Schleife korrigieren könnte:  If sp(j, jj) <> sq(Val(Right(sp(1, jj), 2))) Then sp(j, jj) = sn(j, jj)   'Wert mit Quelle austauchen 
Was aber -NİCHT geht-, weil das "_" Zeichen im nachfolgen Code bei - FormatConditions.Add(2 benötigt wird!!  Das habe ich kapiert!

Dieser Teil mit -Format Condition- funktioniert im Augenblick noch nicht bei mir. Ich verstehe leider garnichts von deiner Super Programmiertechnik, habe von dem Befehl noch nie gehört, um den Fehler selbst finden zu können.

Wie bekomme ich NACH der Farbmarkierung den Austauch der falschen Werte hin?? Muss ich dazu die zweite For Next Schleife noch einmal durchlaufen lassen? Mit der Lösung "Wert mit Quelle austauchen" die ich dazu fand? Oder hast du dazu noch eine bessere Idee??  

Noch mal vielen Dank für eure Hilfe

Mfg  Gast 123

---

Nachtrag:   an snb   habe gerade deine zweiten Code gesehen, lade ihn zum testen runter. Brauche aber etwas Zeit dafür, muss noch zwei Katzen füttern.  miau, miau ...

[snb]

1. Zum übersetzen von Webseiten verwende https://www.deepl.com/translator

2. Verzichte auf diakritische Zeichen (wie z.B. Ü ) in fieldnames (Spaltennnamen).

3. Was sollte wo ausgetauscht werden ? Die Frage war doch nur 'farblich markieren' ?

[Gast 123]

Hallo snb

entschuldige bitte das ich das in meiner Anfrage nicht extra erwaehnt hatte. Es soll in der Zieltabelle farblich markiert, und gegen die Werte der Quelle ausgetauscht werden. Das hatte ich wohl nicht gut genug erklaert.

Dein 2. Makro habe ich gerade getestet, es bleibt aber an dieser Zeile haengen:  gelb markiert   With CreateObject("ADODB.Recordset")
Kann es daran liegen das ich zur Zeit auf einem alten XP Laptop arbeite, weil zwei Samsung Laptops mit Win7/10 Totalschaden haben?
Im Augenblick habe ich leider keinen anderen PC zur Verfügung ...

mfg Gast 123

[snb]

XP-sollte keine Problem sein. (hier getestet)

In VBEdior /extra/references gibt es eine Liste mit 'Bibliotheken'.
Dort muss es ein 'Microsoft ActiveX Data Objects 2.0 Library' geben.