Filter-Makro gesucht

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 14:12:00

Hallo,
die beigefügte Arbeitsmappe enthält 2 Sheets: "Raumdirekt" und "Zuweisung". Die Aufgabenbeschreibung ist inliegend. Vielen Dank schon mal im Voraus.
https://www.herber.de/bbs/user/99902.xlsm
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 14:13:11
Sorry, habe vergessen zu sagen, dass ich es mit dem Makro an Bord schon mal erfolglos getestet habe.

Bild

Betrifft: AW: Filter-Makro gesucht
von: Sepp
Geschrieben am: 30.08.2015 15:43:49
Hallo Herbert,
eine Möglichkeit ohne VBA.
https://www.herber.de/bbs/user/99904.xlsm

Gruß Sepp


Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 16:48:03
Hallo Sepp,
vielen Dank für Deine AGGREGAT-Version. Die hebe ich mir auch auf, denn bisher hatte ich nur eine {}-Version, die ich aber aus V-max-Gründen los werden wollte. Die AGGREGAT-Version ist zwar sehr viel übersichtlicher, aber würde sie auch die Rechenzeit verkürzen, im Verhältnis zur alten Matrix-Version?
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Sepp
Geschrieben am: 30.08.2015 17:00:18
Hallo Herbert,
AGGREGAT() ist sehr schnell!

Gruß Sepp


Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 17:12:56
Hallo Sepp,
geht die AF auch ohne Hilfsspalte?
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Sepp
Geschrieben am: 30.08.2015 17:38:08
Hallo Herbert,
glaube nicht, oder nur sehr umständlich und unübersichtlich.

Gruß Sepp


Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 17:43:41
Schade, dann kann ich sie so oder so nicht anwenden. Aber trotzdem vielen Dank!
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Michael
Geschrieben am: 30.08.2015 15:38:00
Hallo Herbert,
ich habe zuallererst On Error bla auskommentiert, das nervt nur bei der Entwicklung, derweil VBA dann nicht in der fehlerhaften Code-Zeile stehenbleibt.
Mit dem Dictionary bin ich nicht groß vertraut: nach ein bißchen herumprobieren habe ich's aufgegeben.
Anbei ne reine Schleifenlösung, die zwar scheinbar viel zu viel arbeitet, aber bei den vorhandenen Testdaten mit ein paar ms auskommt: bei 530 wird es auch *ohne* "mit abgeschaltetem Alles" nicht "spürbar" brauchen.

Option Explicit
Sub AltlampenInZuweisung()
   Dim dic As Object, arr, z As Long, iLastRow%, sFrage$
   Dim t(0 To 2) As Single
   Dim i&, j&
   Const vonRD = 7, vonZuw = 9
   t(0) = Timer
   
'   On Error GoTo ende
   ' auskommentiert, sonst bleibt der Debugger nicht in der fehlerhaften
   ' Zeile stehen.
   
'   Application.EnableEvents = False
'      Application.Calculation = xlCalculationManual
   
         iLastRow = Cells(Rows.Count, 2).End(xlUp).Row
       
         'RaumDirekt!K$7:K$506
         z = Sheets("RaumDirekt").Cells(Rows.Count, 11).End(xlUp).Row
         arr = Sheets("RaumDirekt").Range("K1:R" & z)
       ' hier war übrigens die .Cells-Referenzierung verkehrt: 1004
       ' ab K *1*, das braucht man zwar nicht, aber die Umrechnerei mit Array/Sheet-Zeilen entfä _
llt
         For i = 1 To z: arr(i, 3) = True: Next   ' zum Überspringen unnötiger Schleifendurchgä _
nge
         For i = vonRD To z
          If arr(i, 3) Then
           For j = i + 1 To z
            If arr(j, 3) Then
             If arr(i, 8) = arr(j, 8) Then arr(j, 3) = False
            End If
           Next
          End If
         Next
         Range("B" & vonZuw & ":B" & iLastRow).ClearContents
         j = 0
         For i = vonRD To z
           If arr(i, 3) Then
             Range("B" & vonZuw + j).Value = arr(i, 1)
             Range("C" & vonZuw + j).Value = arr(i, 8)
          ' nur zum Debuggen
             j = j + 1
           End If
         Next
ende:
t(1) = Timer
MsgBox (t(1) - t(0)) * 1000 & " Millisekunden"
      If Err > 0 Then
         MsgBox "ERROR!"
         Else
            MsgBox "Fertig!"
      End If
      If Application.Calculation = xlCalculationManual Then
         sFrage = MsgBox("Möchten Sie die autom. Berechnung einschalten?", vbYesNo, "Frage")
         If sFrage = vbYes Then Application.Calculation = xlCalculationAutomatic
      End If
   Application.EnableEvents = True
End Sub
Schöne Grüße aus Nbg.,
Michael

Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 16:44:51
Hallo Michael,
vielen Dank, das funzt gut. Aber mich würde auch noch eine Dictionary-Version etc. interessieren. Vielleicht hat ja jemand noch eine Version (Rudi Maintaire, Nonet, Nepumuk, etc. bitte nicht böse sein, wenn ich einen vergessen habe, ihr wisst ja, das Alter!!!) ;o)=)
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 30.08.2015 17:44:35
Nochmal sorry, doch da habe ich den Haken vergessen! ;o)=)

Bild

Betrifft: AW: Filter-Makro gesucht
von: Rudi Maintaire
Geschrieben am: 31.08.2015 13:23:45
Hallo,
wenn ich das richtig verstanden habe:

Sub AltlampenInZuweisungA()
  Dim vArr, i As Long, oDic As Object
  Set oDic = CreateObject("Scripting.dictionary")
  With Sheets("Raumdirekt")
    vArr = .Range(.Cells(7, 11), .Cells(.Rows.Count, 11).End(xlUp)).Resize(, 8)
  End With
  oDic(0) = "Neue Liste"
  For i = 1 To UBound(vArr)
    oDic(vArr(i, 8)) = vArr(i, 1)
  Next
  With Sheets("Zuweisung")
    .Cells(8, 2).CurrentRegion.ClearContents
    If oDic.Count > 1 Then
      .Cells(8, 2).Resize(oDic.Count) = WorksheetFunction.Transpose(oDic.items)
    End If
  End With
End Sub

Gruß
Rudi

Bild

Betrifft: AW: Filter-Makro gesucht
von: Herbert Grom
Geschrieben am: 31.08.2015 16:39:19
Hi Rudi,
viiielen Dank, genau so hatte ich mir das vorgestellt!!! Da sehe ich mal wieder, welche Lücken ich noch habe! Zwar kann ich es nun auch für andere Spalten weiterverwenden, doch verstanden habe ich es noch nicht.
Die Aufgabe ist ja: Füge den Text aus Spalte R zur Spalte K, liste das Ergebnis auf, aber filtere die Doppelten heraus. Wenn ich das richtig sehe, machst Du das mit dieser Zeile:
oDic(vArr(i, 8)) = vArr(i, 1)
Wenn ja, dann kapiere ich nicht wie das geht. Gut, er vergleicht im Array die 1. mit der 8. Spalte, aber der Rest ist ???
Kannst Du mir das bitte kurz erläutern? Vielen Dank schon mal im Voraus.
Servus

Bild

Betrifft: AW: Filter-Makro gesucht
von: Michael
Geschrieben am: 31.08.2015 21:10:19
Hallo Herbert,
ich habe mal ne Reihe Tests gemacht: die Dictionary-Lösung ist einen Hauch schneller. Wenn Du und Rudi nichts dagegen habt bzw. es Euch beide interessiert, lade ich den Vergleich mit dem je neuesten Code mal hoch (500 Testdatensätze, Berechnungsschleife x 100, Ausgabe x 100, in ms).
Die Zeile
oDic(vArr(i, 8)) = vArr(i, 1)
kann ich Dir schon erklären: Dictionary heißt ja so viel wie Wörterbuch, und so ähnlich funktioniert es auch: dem Begriff, der *links* angegeben wird (vArr(i,8) ist die Gruppe aus Spalte R), wird *rechts* die Glühbirne (ja, ja, das Leuchtmittel) aus vArr(i, 1) bzw. Spalte K zugewiesen.
Im Prinzip ist ein Dictionary so was Ähnliches wie ein Array, nur daß der Zugriff nicht über Nr., sondern über Begriffe erfolgt.
Ich hab Dir mal kleine Datei mit je einem Makro in "Tabelle1" (=Kernstück aus Rudis Code) bzw. "Erweiterung" gebastelt: https://www.herber.de/bbs/user/99927.xls
Aber ehrlich gesagt: ich habe die zwei kurzen Tests zwar irgendwie zusammengestöpselt, aber die ganze Geschichte kommt mir eher "sperrig" vor (ein paar Links sind in der Datei).
Naja, man kann sich dann auch gleich mit Klassen beschäftigen: irgendwann hatte ich ne typische Meldung á la "get"- und "let"-Objekte nicht gefunden.
Happy Exceling,
Michael

Bild

Betrifft: AW: Filter-Makro gesucht
von: Rudi Maintaire
Geschrieben am: 31.08.2015 22:40:32
Hallo Herbert,
das ist eigentlich ganz einfach.
Das Dictionary ist ein Schlüssel-Wert-Paar (Keys-Items). So ähnlich wie eine Collection, nur flexibler.
Die Werte können alles mögliche sein. Zahlen, Strings, Array, Objekte, ...
Für den Schlüssel gilt das Highlander-Prinzip: Es kann nur einen geben.
Schlüssel GZ01; Wert: Energiesparlampe, Stecksockel, 2G11, 24W, 2-polig
Das funktioniert hier aber nur, weil jede Gruppe nur einen Artikel enthält (wozu dann eine Gruppe???)
Wenn eine Gruppe mehrere Artikel enthält wird's komplizierter. Dann könnte man die einzelnen Elemente in ein Array schreiben oder, was ich bevorzuge, in einen eindeutig getrennten String (aaa|bbb|ccc), den man später mit der Split()-Funktion in ein Array zerlegt.
Tw. sieht das nach viel Code aus, ist aber rasend schnell. Vor allem, wenn man die Rohdaten vorher in ein Array einliest. Ich denke mal, das das auch bei 50.000 Zeilen in weniger als 1 Sekunde erledigt ist.
Wenn du Bedarf hast, ruf mich einfach mal abends an.
Gruß
Rudi

Bild

Betrifft: Geschwindigkeit
von: Michael
Geschrieben am: 01.09.2015 15:21:04
Hallo zusammen,
daß Scripting Dictionaries (SD) "rasend schnell" sind, hat mir keine Ruhe gelassen.
Letztlich muß ja bei jedem neuen Eintrag überprüft werden, ob er bereits vorhanden ist, damit das SD entscheiden kann, ob der key aufgenommen wird oder nicht.
Ein Hinweis darauf ist steht in http://www.office-loesung.de/ftopic644231_0_0_asc.php
insbesondere im Absatz "Die Methode reduziert die Anzahl der Zugriffe..."
Ich verstehe zwar die Erklärung mit Primzahlen nicht (das Beispielskript war mir jetzt zu umfangreich), aber es scheint sich um so was ähnliches wie einen Index bei ner DB zu handeln - wie auch immer der intern arbeitet.
Gut, gut.
Ich habe leider keine deutsche Anleitung zum Thema SD gefunden, aber
http://www.snb-vba.eu/VBA_Dictionary_en.html
ist recht handlich und bietet ne umfangreiche Beispieldatei.
Happy Exceling,
Michael

Bild

Betrifft: AW: Geschwindigkeit
von: Rudi Maintaire
Geschrieben am: 01.09.2015 20:55:59
Hallo Michael,
erst mal vielen Dank für den Link. Kannte ich nicht.
Das in dem von dir zitierten Thread auf OL hat wenig bis nichts mit dem 'normalen' Dictionary zu tun.
Es handelt sich um einen Nach- und Ausbau des Dictionary-Objekts, so dass es auch unabhängig vom ScriptingHost zur Verfügung steht.
Lies den Artikel mal aufmerksam.
Eine Glanzleistung auf einem Level, das ich in diesem Leben nicht mehr erreichen kann. Das was hier oftmal als umfangreiches Projekt bezeichnet wird, entlockt Isi nur ein müdes Lächeln. Umfangreich fängt bei ihr bei 100.000 Zeilen an. ;-)
Gruß
Rudi

Bild

Betrifft: AW: Geschwindigkeit
von: Michael
Geschrieben am: 02.09.2015 15:45:03
Hallo Rudi,
freut mich; vielleicht sollte man ihn mal übersetzen.
Den OL-Artikel habe ich schon aufmerksam gelesen: Isis *Erklärung*, warum Dics überhaupt so schnell sind, ist ja unabhängig von ihrer Eigenentwicklung.
Ja, die Frau ist echt gut.
Schöne Grüße,
Michael

Bild

Betrifft: evtl doch mißverstanden @Rudi
von: Michael
Geschrieben am: 02.09.2015 18:03:40
Hi Rudi,
vielleicht habe ich's ja doch so mißverstanden, daß "nur" Isi diese Art von Daten- bzw. Indexstruktur (also irgendwie verwurschtelte Primzahlen) zur Beschleunigung verwendet und die scrrun.DLL eine ganz andere.
Ich weiß ja nicht, ob sich MS überhaupt dazu äußert, auf der Suche bin ich erst Mal auf einen erheiternden Dialog gestoßen, den ich Dir nicht vorenthalten möchte:
http://yourdon.com/strucanalysis/wiki/index.php?title=Chapter_10#The_need_for_data_dictionary_notation
Dann gibt es noch
http://www.vbforums.com/showthread.php?801827-Internal-data-structure-amp-algorithm-of-Dictionary-object&p=4913397&viewfull=1
da geht es richtig zur Sache mit Disasm - diese "hyperactiven" Gestalten überschreiten meinen Horizont.
In
http://www.codeproject.com/Articles/500644/Understanding-Generic-Dictionary-in-depth
sind zwar C#-Beispiele aus dem .Net-Ding, aber die fummeln a bissl mir Hashes herum.
Hier in Sachen VBA:
http://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html
Und allg.: https://en.wikipedia.org/wiki/Lookup_table
So, jetzt reicht's! Immer, wenn ich was etwas genauer wissen will, ufert es aus - aber man kann seine Zeit durchaus mit weniger anregenden Sachen als Programmierung (oder Mathe) verbringen - paßt scho!
Gruß,
Michael
P.S.: ich bin natürlich hängengeblieben und habe noch das entdeckt:
https://sysmod.wordpress.com/2011/11/02/dictionary-class-in-vba-instead-of-scripting-dictionary/
Das scheint je nach Anwendung *schneller* als die Original-DLL zu laufen, und zwar sowohl auf W als auch Mäck.

Bild

Betrifft: Filtern ohne Duplikate per VBA
von: NoNet
Geschrieben am: 02.09.2015 14:22:09
Moin Herbert,
für das Filtern ohne Duplikate bietet Excel doch eine eigene Funktion, die man auch per VBA nutzen kann.
Es wird lediglich unterschieden, ob die Filterung an gleicher Stelle (oder im gleichen Blatt) stattfinden soll oder ob das Ergebnis in einem anderen Blatt dargestellt werden soll. Im letzteren Fall muss man den zu filternden Bereich (im Blatt "RaumDirekt") zunächst benennen.
Hier die beiden VBA-Varianten :

Sub FilterOhneDuplikateGleicheStelle()
    'Filtert die Originalliste an der gleichen Stelle :
    Range("K6:R17").RemoveDuplicates Columns:=Array(1, 8), Header:=xlYes
End Sub

Sub FilterOhneDuplikateAnderesBlatt()
    'Bereich in Blatt "RaumDirekt" zunächst benennen :
    'ActiveWorkbook.Names.Add Name:="ListeRaumDirekt", RefersTo:="='RaumDirekt'!$K$6:$R$17"
    
    'Zielblatt auswählen
    Sheets("Zuweisung").Select
    
    'Filter von ZIEL-Blatt aus ausführen
    Range("ListeRaumDirekt").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B33"),  _
Unique:=True
End Sub
Salut, NoNet
PS: Du erhältst in Kürze noch eine Mail von mir zu einem anderen "Thema" ;-)

Bild

Betrifft: AW: Filtern ohne Duplikate per VBA
von: Herbert Grom
Geschrieben am: 02.09.2015 15:36:11
Hallo NoNet,
mercie vielmals, doch das geht so leider nicht, denn es darf nur der Text der Spalte K in die Spalte B übernommen werden. Der gefilterte Text natürlich. Aber trotzdem vielen Dank, denn mit Deiner Codezeile zum Text einfügen habe ich schon wieder was gelernt! ;o)=)
Der Code von Rudi funzt prächtig! Hier ist er für Dich und alle anderen auch:

Sub vonRudi()
   Dim vArr, i As Long, j As Integer, oDic(1 To 6), sKey As String, arrCol
   
   '* Spaltenreihenfolge
      arrCol = Array(, 2, 1, 3, 4, 5, 6)
   
   '* Dictionarys setzen
      For i = 1 To 6
         Set oDic(i) = CreateObject("scripting.dictionary")
      Next
   
   '* Daten in Array
      With RaumDirekt
         vArr = .Range(.Cells(7, 11), .Cells(Rows.Count, 11).End(xlUp)).Resize(, 8)
      End With
   
   '* Daten aus Array in Dictionarys einlesen
      For i = 1 To UBound(vArr)
         sKey = vArr(i, 8) 'Schlüssel
         For j = 1 To UBound(arrCol)
            oDic(j)(sKey) = vArr(i, arrCol(j))  'Spaltenwerte
         Next
      Next i
   
   '* Daten in Tabelle schreiben
      With Zuweisung
         .Range(.Cells(9, 1), .Cells(108, 6)).ClearContents
         .Cells(9, 11).Resize(oDic(1).Count) = WorksheetFunction.Transpose(oDic(1).keys)
         For j = 1 To 6
            .Cells(9, j).Resize(oDic(1).Count) = WorksheetFunction.Transpose(oDic(j).items)
         Next
      End With
End Sub
Servus
P.S.: eMail ist noch nicht eingetroffen!

Bild

Betrifft: AW: Filtern ohne Duplikate per VBA
von: Daniel
Geschrieben am: 02.09.2015 17:19:43
Hi
es hindert dich ja nichts daran, erstmal die vollständige Spalte K und R zunächst erstmal an einen freien Bereich der Tabelle zu kopieren, dort die Daten passend zu sortieren und das RemoveDuplicates auszuführen um dann die so überareitete Liste an den eigentlichen Zielort zu übertragen.
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Mehrere geöffnete Dateien abfragen"