Anzeige
Archiv - Navigation
1200to1204
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

mehrspaltiges Listenfeld sortieren

mehrspaltiges Listenfeld sortieren
Wastl
Hallo und guten Morgen
Ich habe in einer Variablen - ich nenne sie VX - eine Tabelle mit ca. 165.500 Zeilen und 13 Spalten.
Als erstes möchte ich gerne die Zeilen nach der 5. Spalte - die erste Zeile enthält die Überschriften der Spaltenköpfe - sortieren.
Ich hab irgendwo gelesen, dass, wenn man eine Listbox mit der Eigenschaft 'Sorted' verwendet, jeder neue Eintrag automatisch einsortiert wird.
Bis jetzt habe ich immer nur mit Listboxen auf einer Userform gearbeitet.
Ich benötige aber eigentlich nur die Listbox, ohne userform. Geht das?
Nach was sortiert die Listbox meine Einträge?
Muss ich die Einträge von VX(x, 5) in die Listbox in die erste Spalte übernehmen?
Wie fülle ich eine Listbox mit den Werten meiner VX?
2. Teil der Frage:
in Spalte E (die 5. Spalte meiner VX) sind momentan 83 verschiedene Typen enthalten.
ich möchte nachher gerne für jeden Typ ein eigenes Tabellenblatt haben. Es bleibt kein Rest.
Wie ich den Inhalt der Variablen VX auf einmal in ein Tabellenblatt schreibe weiß ich, wenn VX weniger als 65536 Zeilen hat. Aber ich gehe davon aus, dass die 165.500 Zeilen sich einigermaßen gleichmäßig auf die 83 Typen verteilen.
Vielen Dank für ein brauchbares Codebeispiel
Wastl
AW: mehrspaltiges Listenfeld sortieren
27.02.2011 09:05:30
Josef

Hallo Wastl,
woher kommt diese Variable VX?
Wozu willst du diese zuerst in eine Listbox einlesen?
Wäre es nicht einfacher, dieses Array gleich auf die einzelnen Tabellen zu verteilen und dann die Tabellen zu sortieren?

Gruß Sepp

AW: mehrspaltiges Listenfeld sortieren
27.02.2011 11:16:02
Wastl
Hi,
Der Datenauszug, den ich da wöchtentlich bearbeite, erhalte ich aus der Quelle in Form einer 53 MB großen XML-Datei, welche ich in Excel auslese und per Replace() die unnötigen Zeichen entferne wie Wert Tags und nur die Werte übrig lässt. Die Länge der Stringvariablen sinkt dabei von 54M auf etwa 18M Zeichen, die ich dann anschließend in die VX-Variable in 13 Spalten aufteile, so wie es in der Quelle auch war. Soweit funktioniert es wunderbar innerhalb 2 Minuten.
Das ist die Vorbedingung zu meiner ersten Frage in diesem Forum. Eine ähnliche (aber mittlerweile teilweise überholte) Frage habe ich bei online-excel.de eingestellt, aber die haben ja GottseiDank nix gegen Crossposting.
Es sind in Spalte E ungefähr 83 verschieden Typen enthalten.
Ein Kollege von mir schreibt erst über Schleifen Hilfstabellen voll mit jeweils 6000 Zeilen und sortiert das Ganze dann anschließend in die 83 Tabellenblätter mit den einzelnen Typen. Das funktioniert sehr gut, dauert aber für diesen einen Umfang auf einem normal ausgestatteten Arbeitsrechner mit Dualcore 2000 MHz und 2 GB Arbeitsspeicher deutlich länger als eine halbe Stunde.
(aber nur wenn Excel als Solitär, also als einziges Programm, aktiv ist und alle anderen Anwendungen geschlossen sind. In dieser Zeit kann man dann halt an dem Rechner nix mehr machen)
Ich wollte das schneller machen, in dem ich die Datenvariable 'Vx' einsetze und erst auf die Tabellen losgehen, wenn ich es, rein nur im Arbeitspeicher, in der richtigen Form habe.
Anzeige
AW: mehrspaltiges Listenfeld sortieren
27.02.2011 11:23:54
Josef

Hallo Wasti,
ich würde VX in einer routine durchlaufen und je nach Typ in 83 arrays verteilen, diese Arrays würde ich dann in die entsprechenden Tabellen schreiben und sortieren.

Gruß Sepp

AW: mehrspaltiges Listenfeld sortieren
27.02.2011 11:39:11
Wastl
Hi Sepp,
in Spalte 5 liegen die Typen nicht sortiert vor. Und dass es 83 verschiedene sind, weiss ich auch nicht wirklich aus meiner Datei, sondern habe ich von jemand anderem 'gehört'.
Ich müsste als erstes einmal wissen, wieviele Typen in der 5 Spalte tatsächlich enthalten sind, dann die entspechende Anzahl von Arrays bereitstellen um diese dann mit den Werten füllen.
Hast du mir dafür ein Code-Beispiel, dass ich dann auf meine Bedürfnisse anpassen kann?
Herzlichen Dank im Voraus
Anzeige
AW: mehrspaltiges Listenfeld sortieren
27.02.2011 12:16:32
Josef

Hallo Wasti,
ich werde später mal darüber nachdenken und dir einen Code erstellen, jetzt hat aber meine Frau zu mir und meinem Sohn die bösen Worte "Kinderzimmer aufräumen" gesagt.
Werde mich also erstmal durch das Chaos aus Techniklego, Kuscheltieren und div. Krimskrams wühlen;-((

Gruß Sepp

AW: mehrspaltiges Listenfeld sortieren
27.02.2011 12:45:45
Wastl
Hi Seppi
mir gehts ähnlich, ich hab gerade die bösen Worte gehört:
"Hilfst du mir beim Kochen?"
Bis dahin hab ich mir dem Quicksort von nepomuk herumgespielt und bin am einbauen in meinen Code.
Aber jetzt erst mal die wichtigen familiären Dinge...
Anzeige
Thema geschlossen, Dank an alle
27.02.2011 15:43:55
Wastl
Danke an alle,
ich habs im Prinzip.
Der Tipp - und der Link - von Christian hat mich wesentlich weitergebracht.
Noch ein paar kleine Feinheiten dann will ich es auf meinem Netbook testen.
Auf meiner privaten Kiste läuft das makro knappe 5 Minuten, dann sind alle 83 Tabellenblätter ausgefüllt. (es sind tatsächlich 83 verschiedene Typen in Spalte 5 enthalten)
Ich hab allerdings keine 83 Variablen gestrickt, sondern nehme immer die selbe, die ich, nachdem in die Tabelle geschrieben wurde, redimensioniere.
AW: Thema geschlossen, Dank an alle
27.02.2011 17:36:27
Josef

Hallo Wastl,
hier doch noch mein Senf, braucht bei 150.000 Zeilen und 80 Typen ~18 Sekunden.
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Sub test()
  Dim VX As Variant
  
  VX = Sheets("Tabelle1").Range("A1:M150000") 'nur zum Testen, du übergibst dein Array!
  
  wastl VX, 5 'Array und die Spalte mit der Typenkennzeichnung übergeben!
End Sub

Sub wastl(Field As Variant, compareColumn As Long)
  Dim objSh As Worksheet, myCol() As New Collection
  Dim vntSheets As Variant, vntTmp() As Variant, vntOut() As Variant
  Dim lngRow As Long, lngCol As Long, lngIndex As Long, lngTmp As Long
  Dim vntRet As Variant, lngCalc As Long
  Dim t As Long
  
  On Error GoTo ErrExit
  
  With Application
    .ScreenUpdating = False
    .EnableEvents = False
    lngCalc = .Calculation
    .Calculation = xlCalculationManual
  End With
  
  Redim vntTmp(LBound(Field, 1) To UBound(Field, 1))
  
  For lngRow = LBound(Field, 1) To UBound(Field, 1)
    vntTmp(lngRow) = Field(lngRow, compareColumn)
  Next
  
  vntSheets = UniqueList(vntTmp)
  
  Redim myCol(UBound(vntSheets))
  
  Erase vntTmp
  
  Redim vntTmp(1 To UBound(Field, 2))
  
  For lngRow = LBound(Field, 1) To UBound(Field, 1)
    vntRet = Application.Match(Field(lngRow, compareColumn), vntSheets, 0) - 1
    For lngCol = LBound(Field, 2) To UBound(Field, 2)
      vntTmp(lngCol) = Field(lngRow, lngCol)
    Next
    myCol(vntRet).Add vntTmp
  Next
  
  For lngIndex = 0 To UBound(vntSheets)
    If SheetExist(vntSheets(lngIndex)) Then
      Set objSh = Sheets(CStr(vntSheets(lngIndex)))
      With objSh.Range("A1")
        .CurrentRegion.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, .CurrentRegion.Columns.Count).ClearContents
      End With
    Else
      Set objSh = ThisWorkbook.Worksheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
      objSh.Name = CStr(vntSheets(lngIndex))
    End If
    
    Redim vntOut(1 To myCol(lngIndex).Count, 1 To UBound(Field, 2) + IIf(LBound(Field, 2) = 0, 1, 0))
    
    For lngCol = 1 To myCol(lngIndex).Count
      vntTmp = myCol(lngIndex).Item(lngCol)
      For lngTmp = 1 To UBound(vntTmp)
        vntOut(lngCol, lngTmp) = vntTmp(lngTmp)
      Next
    Next
    
    objSh.Range("A2").Resize(UBound(vntOut, 1), UBound(vntOut, 2)) = vntOut
  Next
  
  ErrExit:
  
  If Err.Number <> 0 Then
    MsgBox "Fehler:" & vbTab & Err.Number & vbLf & vbLf & Err.Description, vbExclamation, "Fehler"
  End If
  
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = lngCalc
  End With
  
  Set objSh = Nothing
  
End Sub

Private Function SheetExist(ByVal sheetName As String, Optional Wb As Workbook) As Boolean
  Dim wks As Worksheet
  On Error GoTo ERRORHANDLER
  If Wb Is Nothing Then Set Wb = ThisWorkbook
  For Each wks In Wb.Worksheets
    If LCase(wks.Name) = LCase(sheetName) Then SheetExist = True: Exit Function
  Next
  ERRORHANDLER:
  SheetExist = False
End Function

Private Function UniqueList(Matrix As Variant, Optional Sorted As Boolean = True) As Variant
  Dim objDic As Object, varTmp() As Variant, lngIndex As Long
  
  Set objDic = CreateObject("Scripting.Dictionary")
  
  For lngIndex = LBound(Matrix) To UBound(Matrix)
    objDic(Matrix(lngIndex)) = 0
  Next
  
  varTmp = objDic.keys
  
  If Sorted Then QuickSort varTmp
  
  UniqueList = varTmp
  
  Set objDic = Nothing
End Function

Private Sub QuickSort(Data() As Variant, Optional UG, Optional OG)
  Dim P1&, P2&, T1 As Variant, T2 As Variant
  
  UG = IIf(IsMissing(UG), LBound(Data), UG)
  OG = IIf(IsMissing(OG), UBound(Data), OG)
  
  P1 = UG
  P2 = OG
  T1 = Data((P1 + P2) / 2)
  
  Do
    
    Do While (Data(P1) < T1)
      P1 = P1 + 1
    Loop
    
    Do While (Data(P2) > T1)
      P2 = P2 - 1
    Loop
    
    If P1 <= P2 Then
      T2 = Data(P1)
      Data(P1) = Data(P2)
      Data(P2) = T2
      P1 = P1 + 1
      P2 = P2 - 1
    End If
    
  Loop Until (P1 > P2)
  
  If UG < P2 Then QuickSort Data, UG, P2
  If P1 < OG Then QuickSort Data, P1, OG
  
End Sub


Gruß Sepp

Anzeige
das sollte mal gesagt werden
05.03.2011 07:07:29
Ass
Hallo Sepp
P E R F E K T !
Gruß
Rudi
AW: mehrspaltiges Listenfeld sortieren
27.02.2011 09:24:28
Christian
Hallo Wastl,
Ich habe in einer Variablen - ich nenne sie VX - eine Tabelle
klingt sehr nach 'nem 2-dimensionalen Array.
...wenn man eine Listbox mit der Eigenschaft 'Sorted' verwendet...
nein, die Listbox in VBA hat keine Eigenschaft 'Sorted'.
Vorschlag: sortiere das Array. Zum Bsp. mit Quicksort von Nepumuk:
http://www.online-excel.de/excel/singsel_vba.php?f=97
Ich benötige aber eigentlich nur die Listbox, ohne userform. Geht das?
theoretisch ja, du kannst das Steuerelement auch in der Tabelle platzieren - da halte ich aber nicht viel von.
Wie fülle ich eine Listbox mit den Werten meiner VX?
übergebe das Array an die Listbox z.Bsp mit "Listbox1.List = VX"
Zu 2:
Da das Array ja jetzt sortiert ist, durchlaufe dieses von "hinten" nach "vorne" und schreibe die Daten in ein weiteres Array (z.B. "vntTemp"), solange der Eintrag in der 5.Spalte gleich bleibt. vntTemp kannst du dann wieder auf einen Schlag in die Tabelle schreiben und diese Daten im Array"VX" mir "Redim Preserve VX(..., ...)" hinten abschneiden.
In 'ner Schleife das ganze wiederholen solange in VX noch Daten vorhanden sind.
Gruß
Christian
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige