excel vba/excel formules

[MARINA111]

Beste

In bijlage stuur ik een voorbeeldje om het te verduidelijken:
Via een invulformulier worden datums, namen en aanwezigheidscodes ingegeven (en nog wat extra informatie).
Aan de hand van die gegevens kunnen op naam / op datum ... draaitabellen worden gemaakt, geen probleem
Maar het tabblad samenvatting werkt heel traag als er veel gegevens zijn
Op het tabblad samenvatting (per werking) wordt van die invullijst een overzicht per week/maand gemaakt met in de a-kolom alle verschillende namen die in die maand een code hebben, in de rij de datums van die week/maand en in de kruising telkens de aanwezigheidscode.
Ik werk hiervoor via "hulp"draaitabellen en index-vergelijken met 2 voorwaarden, maar ik vind het omslachtig en als er veel gegevens zijn werkt het heel traag. Toch heb ik de lijst op die manier nodig.
Heeft iemand een oplossing die veel efficiënter is??

 

[Timshel]

In ieder geval kan de matrixformule efficiënter. In plaats van de hele kolom als bereik op te geven kun je beter een kleiner bereik gebruiken.
In bijlage is één draaitabel weggewerkt en worden gegevens direct uitgelezen uit de brontabel. Met gegevensvalidatie kun je een werking selecteren:

 

[MARINA111]

Bedankt !! Het werkt in ieder geval al veel sneller.
Alleen, nu staan in de eerste kolom nog alle namen die in de basislijst voorkomen,
het zou de bedoeling zijn om enkel die namen te hebben die in die werking voorkomen.

 

[Timshel]

Met een stukje VBA:

 

[MARINA111]

Schitterend !! Heel erg bedankt.

 

[MARINA111]

sorry, maar ik begrijp de code toch niet helemaal, als ik het in m'n werkblad wil invoegen werkt het niet; de kolommen en rijen zijn niet allemaal dezelfde. Heb al één en ander geprobeerd, maar ik krijg die namen er maar niet in.
Is het grote moeite om je code wat toe te lichten?

 

[Timshel]

Het is natuurlijk wel zaak dat je een gelijkend voorbeeld plaatst. Ik kan niet zien hoe je gegevens gepositioneerd zijn voor zover ze afwijken van je voorbeeld.
Plaats de code in de werkbladmodule 'samenvatting', of het overeenkomende werkblad in het echte bestand. Dus niet in een gewone module.
Onderstaande code is enigszins aangepast: de regel

On Error Resume Next

onderdrukt foutmeldingen, dus die heb ik eruit gehaald:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim Br
    
    If Target.Address = "$B$1" Then
        Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        If Target = "" Then Exit Sub
        Br = Sheets("invulblad").Cells(1).CurrentRegion 'Lees gegevens in van werkblad "invulblad", vanaf cel A1.
        With CreateObject("Scripting.Dictionary")
            For i = 2 To UBound(Br)
                [COLOR="#FF0000"]If Br(i, 12) = Target Then .Item(Br(i, 6)) = 0[/COLOR] 
            Next
            Range("A3").Resize(.Count) = Application.Transpose(.Keys)
        End With
    End If
End Sub

Levert dit foutmeldingen op?
De rode regel is de essentie van de routine. De index 12 komt overeen met de 12e kolom, ofwel L. Index 6 is kolom F. Dit moet overeenkomen met de positie van gegevens in het invulblad.
Als het nog niet lukt, plaats dan een nieuw voorbeeld dat qua gegevensindeling en werkbladbenaming exact overeenkomt met het origineel.

 

[MARINA111]

Beste

Als ik zorg dat m'n invulblad start in a1 lukt het, maar er staat nog een titel en een blanco lijn en dan pas de tabel....

 

[Timshel]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim Br
    
    If Target.Address = "$B$1" Then
        Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        If Target = "" Then Exit Sub
        Br = Sheets("invulblad").[COLOR="#FF0000"]Cells(3, 1)[/COLOR].CurrentRegion
        With CreateObject("Scripting.Dictionary")
            For i = 2 To UBound(Br)
                If Br(i, 12) = Target Then .Item(Br(i, 6)) = 0
            Next
            Range("A3").Resize(.Count) = Application.Transpose(.Keys)
        End With
    End If
End Sub

 

[MARINA111]

Ok, is gelukt nu en 'k denk dat ik het ongeveer begrijp.
Heel erg bedankt !!:thumb:

 

[MARINA111]

Beste

Nog 1 klein vraagje : ik zou die namen nog op de meest efficiënte manier alfabetisch willen sorteren.
(het gaat om een grote lijst, vandaar nog eens de vraag)

 

[Timshel]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    Dim Br
    Dim Rng As Range
    
    If Target.Address = "$B$1" Then
        Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
        If Target = "" Then Exit Sub
        Br = Sheets("invulblad").Cells(3, 1).CurrentRegion
        With CreateObject("Scripting.Dictionary")
            For i = 2 To UBound(Br)
                If Br(i, 12) = Target Then .Item(Br(i, 6)) = 0
            Next
            Set Rng = Range("A3").Resize(.Count)
            Rng = Application.Transpose(.Keys)
            Rng.Sort [A3], xlAscending
        End With
    End If
End Sub

 

[MARINA111]

Heel erg bedankt!! Het werkt echt wel heel veel sneller.
Ik ben heel tevreden!!
Eigenlijk vertrek ik uit verschillende bestanden die ik kopieer naar een nieuw bestand om dan die lijsten te kunnen maken.
Zou die samenvatting ook gemaakt kunnen worden rechtstreeks uit die aparte bestanden - NIET geopend?

 

[Timshel]

Het is mogelijk om geautomatiseerd gegevens in te lezen uit andere excelbestanden. Die moeten dan wel geopend worden, tenminste op de achtergrond.

 

[MARINA111]

Zou hiervan eens een voorbeeldje kunnen posten, of moet ik hieromtrent een nieuwe vraag stellen?

 

[Timshel]

Dat past nog bij dit topic.
Kom maar door met je voorbeeldje :d

 

[MARINA111]

Beste

In bijlage stuur ik een voorbeeldje, met een woordje uitleg.
Tot op heden werk ik altijd met een macro waarbij ik de detailgegevens uit verschillende bestanden kopieer en plak naar een nieuw bestand om daar dan totalen te kunnen maken, bijgevolg moet die macro telkens eerst uitgevoerd worden om aan de meest recente totalen te komen.
Ik zou heel blij zijn met een efficiëntere werkwijze, want het gaat wel over zo'n 30 verschillende bestanden.

 

[Timshel]

Ik zou zeggen dat je hier aanloopt tegen de grenzen van wat mogelijk is met Excel.
Excel kan, bij mijn weten, geen dataverbindingen leggen met of gegevens importeren uit andere excelbestanden, zoals dat bijvoorbeeld wel kan met een Access-tabel.
Een macro die gegevens kopieert zoals jij al hebt is volgens mij nog de beste oplossing. Je macro kan mogelijk wel vlotter geprogrammeerd worden. Als je hem plaatst kan ik of een andere helper ernaar kijken.
Voor een echt efficiëntere aanpak zou je moeten kijken naar Access of een andere databaseoplossing.