Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1540to1544
1540to1544
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

große Datenmenge zuordnen / Array???

Forumthread: große Datenmenge zuordnen / Array?

große Datenmenge zuordnen / Array?
15.02.2017 17:04:44
Jörg
Hallo,
ich habe eine Datei mir ca. 500.000 Zeilen...
- In Spalte A sind Werte die sich wiederholen (unterschiedlich oft)
- In Spalte B sind in jeder Zeile unterschiedliche Werte
Ich möchte nun bei jeden neuen Wert (also erster neuer Wert) in Spalte A die zugehörigen Werte der Spalte B in eine Zelle Spalte C schreiben
z.B.
Spalte A_____Spalte B___________Spalte C
1_______________5_______________5, 3, 8, 2
1_______________3_______________
1_______________8_______________
1_______________2_______________
2_______________4_______________4, 2, 7
2_______________2_______________
2_______________7_______________
Aufgrund der großen Datenanzahl dauert das mit einer Schleife leider ewig...
Anzeige

14
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Array und dictionary
15.02.2017 17:27:03
Fennek
Hallo,
nur als Ansatz:
Spalten A und B in ein Array übergeben und dann mit "Dictionary" zusammenfassen. Es sollte nicht viel länger als 1-2 Minuten brauchen.
mfg
AW: Array und dictionary
15.02.2017 18:02:45
Jörg
Hallo Fennek,
vielen Dank für die schnelle Antwort!
Allerdings bin ich damit überfordert... den Array bekomme ich glaube ich noch hin (bin mir da aber auch nicht 100%ig sicher)
Aber "Dictonary"...? :-)
Anzeige
Wenn du dictionary richtig schreibst, bekommst ...
15.02.2017 18:40:48
Luc:-?
…du auch genügend Treffer in Archiv und Netz, Jörg,
und außerdem in der VBE-Hilfe.
Luc :-?
Ewig? Alle Störfaktoren ausgeschaltet? Schleife …
15.02.2017 17:39:35
Luc:-?
…optimal gebildet, Jörg?
Ansonsten geht's auch mit Fmln, sogar Xl-Standard-Fktt bei kumulativer Bildung von Zeile zu Zeile. Neuanfang dann immer bei Wechsel in A (Bsp im Archiv und auch aktuell im Forum → zumeist von Daniel vorgeschlagen).
Komplettlösung (ohne kumulatives Vorgehen) effizient ggf nur mit neuer Xl-Fkt TEXTVERKETTEN (nur im Xl-Abonnement) oder einer UDF ala Verketten2 & Co. Meine UDF VJoin hat mit ca 1000 Daten kein Problem, aber hier sind's sicher weniger, wenn man die Fml quasi filternd aufbaut.
Gruß, Luc :-?
Auch lesenswert…
Anzeige
AW: große Datenmenge zuordnen / Array?
15.02.2017 21:25:20
Daniel
Hi
da die Daten offensichtlich sortiert sind, sollte das mit einem einfachen Schleifendurchlauf funktionieren.
Ein Dictionary wird nicht benötigt.
hier der Code, braucht bei mir c.a. 2 Sekunden für 500.000 Zeilen
Sub xxx()
Dim arr1
Dim arr2
Dim z As Long
Dim X As String
arr1 = Cells(1, 1).CurrentRegion.Value
ReDim arr2(1 To UBound(arr1, 1), 1 To 1)
For z = UBound(arr1, 1) To 2 Step -1
X = arr1(z, 2) & ", " & X
If arr1(z, 1)  arr1(z - 1, 1) Then
arr2(z, 1) = Left(X, Len(X) - 2)
X = ""
End If
Next
X = arr1(1, 2) & ", " & X
arr2(1, 1) = Left(X, Len(X) - 2)
Cells(1, 3).Resize(UBound(arr2, 1), 1) = arr2
End Sub

wie sah den dein Code aus?
Anzeige
AW: große Datenmenge zuordnen / Array?
16.02.2017 06:58:59
Jörg
Hallo Daniel,
vielen Dank für deine Antwort. Das Makro läuft super und auch in einer wahnsinnigen Geschwindigkeit.
Allerdings würde mich die Lösung über ein Dictionary trotzdem noch interessieren (rein Interesse halber)
Mein ursprünglicher Code sah wie folgt aus:
letzteZeile = Cells(1, 1).End(xlDown).Row
For I = 2 To letzteZeile
a = 1
auftrag_Kopie = Range("A" & I).Value
auftrag_Kopie1 = Range("A" & I + 1).Value
auftrag_Kopie2 = Range("A" & I - 1).Value
If auftrag_Kopie = auftrag_Kopie2 Then
Else
Zusammenfassung = Range("B" & I).Value
Range("C" & I).Value = Zusammenfassung
Do Until auftrag_Kopie auftrag_Kopie1
Zusammenfassung = Zusammenfassung & ", " & Range("B" & I + a).Value
Range("C" & I).Value = Zusammenfassung
a = a + 1
auftrag_Kopie1 = Range("A" & I + a).Value
Loop
End If
Next
Anzeige
AW: große Datenmenge zuordnen / Array?
16.02.2017 09:13:52
Daniel
Hi
mit Dictionary könnte das so aussehen.
das Dictionary wäre dann das Mittel der Wahl, wenn die Liste nicht sortiert ist.
Sub Test_Dic()
Dim dic As Object
Dim arr1, arr2
Dim z As Long
arr1 = Cells(1, 1).CurrentRegion.Value
ReDim arr2(1 To UBound(arr1, 1), 1 To 1)
Set dic = CreateObject("Scripting.dictionary")
For z = 1 To UBound(arr1, 1)
dic(arr1(z, 1)) = dic(arr1(z, 1)) & ", " & arr1(z, 2)
Next
For z = 1 To UBound(arr1, 1)
arr2(z, 1) = Mid(dic(arr1(z, 1)), 3)
dic(arr1(z, 1)) = ""
Next
Cells(1, 4).Resize(UBound(arr2, 1), 1) = arr2
End Sub
Gruß Daniel
Anzeige
AW: große Datenmenge zuordnen / Array?
16.02.2017 09:21:33
Jörg
Hallo Daniel,
funktioniert ebenfalls super... läuft allerdings (was ich nicht gedacht hätte) etwas länger.
vielen Dank nochmals für die tolle Unterstützung!!!
AW: große Datenmenge zuordnen / Array?
16.02.2017 10:26:49
Daniel
Hi
ist doch logisch dass das länger läuft.
du brauchst zwei Schleifen:
die erste ermittelt die Daten
die zweite trägt die Daten ein
hinzu kommt dann noch die Suchzeit im Dictionary.
die erste Variante für Sortierte Daten kommt mit einer Schleife aus und muss das Zwischenergebnis nicht speichern, sondern kann es direkt verwenden und dann vergessen.
Gruß Daniel
Anzeige
AW: Danien ist schneller: 4x
16.02.2017 14:23:41
Fennek
Hallo,
im Sinne eines kleinen Rennens habe ich einen entsprechenden Code mit dictionary geschrieben.
Das Ergebnis: Daniels code (den ich nicht so richtig verstehe) ist 4 x schneller.
Das Setting:

Sub anlegen()
ActiveSheet.Cells.Clear
For i = 1 To 10000
lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
Range(Cells(lr, "A"), Cells(lr, "A").Offset(4)) = i
Range(Cells(lr, "B"), Cells(lr, "B").Offset(4)) = Application.Transpose(Array(1, 2, 3, 4, 5) _
)
Next i
Range("A1:B1") = Array(1, 23)
End Sub
Sub Daniel()
Anf = Timer
Dim arr1
Dim arr2
Dim z As Long
Dim X As String
arr1 = Cells(1, 1).CurrentRegion.Value
ReDim arr2(1 To UBound(arr1, 1), 1 To 1)
For z = UBound(arr1, 1) To 2 Step -1
X = arr1(z, 2) & ", " & X
If arr1(z, 1)  arr1(z - 1, 1) Then
arr2(z, 1) = Left(X, Len(X) - 2)
X = ""
End If
Next
X = arr1(1, 2) & ", " & X
arr2(1, 1) = Left(X, Len(X) - 2)
Cells(1, 3).Resize(UBound(arr2, 1), 1) = arr2
Debug.Print "Daniel", Timer - Anf
End Sub
Sub Fen()
Anf = Timer
lr = Cells(Rows.Count, "A").End(xlUp).Row
F0 = Range("A1:B" & lr)
With CreateObject("scripting.dictionary")
For i = 1 To lr
If Not .exists(Cells(i, "A").Value) Then
.Add (F0(i, 1)), F0(i, 2)
Else
.Item(F0(i, 1)) = .Item(F0(i, 1)) & ", " & F0(i, 2)
End If
Next i
Cells(1, 4).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
End With
Debug.Print "Fen", Timer - Anf
End Sub
mfg
Anzeige
AW: Danien ist schneller: 4x
16.02.2017 15:16:21
Daniel
Hi
wenn du auf ein kleines Rennen aus bist, dann fangen wir mal mit dem Aufbau des Testpieces an.
teste mal das hier für den Aufbau der Liste:
with Range("A1:B10000") 'größe ggf anpassen
.Columns(1).Formula = "=Int((row()-1)/5)+1"
.columns(2).Formula = "=Mod(Row()-1,5)+1"
.Copy
.PasteSpecial xlpastevalues
end with
Gruß Daniel
Anzeige
AW: ebenso: Daniel ist schneller: 4x
16.02.2017 15:41:24
Fennek
Hallo,
kein Unterschied, Daniel's Arrays sind 4 x schneller als Dict.
mfg
AW: ebenso: Daniel ist schneller: 4x
16.02.2017 15:48:14
Daniel
du solltest nicht die Arrays testen und vergleichen, sondern die Makros zum Aufbau der Testdatei.
Gruß Daniel
AW: ebenso: Daniel ist schneller: 4x
16.02.2017 16:04:10
Fennek
Hi,
dein Code ist 40x schneller!
mfg
Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige