Somproduct te langzaam voor grote hoeveelheid data

[Marccuriel]

Hallo,

Ik zit met het volgende issue: ik moet een waarde (tarief) ophalen uit een andere tabel. In de stamdata staat het tarief met een van - tot datum. In het werkblad heb ik de datum van verkoop. Nu moet ik deze twee bij elkaar zoeken. Dit levert bij een groot aantal regels (>150.000) problemen op. Ik heb dit getracht op te lossen met een matrix (index) formule, maar deze gaat niet goed om met de datum range. Ik hoop dat iemand mij kan helpen.

Alvast bedankt.

Bekijk bijlage Voorbeeld Somproduct.xlsx

 

[sylvester-ponte]

Hallo Marc,
Is het zo dat per Mdw de tijden altijd aanstuitend zijn,zo als nu in het voorbeeld?
Dus dat de einddatum van een Mdw altijd aansluit op de begin datum van de volgende regel van die Mdw.
Is het mogelijk de brontabel te sorteren?
En is het goed om evt. VBA te gebruiken?

 

[sylvester-ponte]

hier een voorbeeld:
de tabel is gesorteerd op Mdw en begindatum
dat is nodig voor deze functie

het kan ook zonder vba maar dit vind ik makkerlijker

 

[Marccuriel]

Beste Sylvester,

Bedankt voor je oplossing, ik ga hier mee aan de slag. Ik heb wel nog een vraag.

Wat gebeurt er wanneer de MDW codes (en data) door elkaar staan? Werkt deze macro dan ook nog?

 

[Marccuriel]

om antwoord te geven op jouw vraag,

Is het zo dat per Mdw de tijden altijd aanstuitend zijn,zo als nu in het voorbeeld? ja dat is zo in het bronbestand
Dus dat de einddatum van een Mdw altijd aansluit op de begin datum van de volgende regel van die Mdw.

Is het mogelijk de brontabel te sorteren? Nee, niet in alle gevallen
En is het goed om evt. VBA te gebruiken? Zeker, ookal heb ik hier persoonlijk weinig ervaring mee

Ik probeer nu jou VBA functie te begrijpen, maar ik kom er niet helemaal uit. Ik denk dat ik dit heel goed kan gebruiken, wanneer ik de verwijzingen naar de bron begrijp.

 

[sylvester-ponte]

Marc,
Waarom is het niet altijd mogelijk om de brontabel te sorteren?
En als de brontabel niet gesorteerd is, staan dan wel alle zelfde Id nummers bij elkaar?
En als dat zo is staan dan de begindatums van één persoon van hoog naar laag gesorteerd?
Als het bronbestand aan de laatste 2 vragen voldoet werkt deze function ook.
Hier de functie met uitleg:

Function Tarief(Mdw As String, Dat, BronBestand As Range)
    Dim Bron As Range, A1, A2, A3
    Set Bron = BronBestand.Columns(1)
        'bron is de gehele eerste kolom van BronBestand
    A1 = WorksheetFunction.Match(Mdw, Bron, 0)
        'A1 geeft de eerste rij aan waar de Mdw staat
    Set Bron = Range(Bron.Cells(A1, 1), Bron.Cells(Bron.Cells.CountLarge, 1))
        'bron loopt nu nog maar van A1 tot eind
    A2 = WorksheetFunction.Match(Mdw, Bron, 1)
        'A2 geeft de laatste Mdw aan
    Set Bron = Range(Bron.Cells(1, 2), Bron.Cells(A2, 4))
        'Bron bevat nu alleen de Rijen van juiste medewerker en bevat nu alleen de kolommen beginDatum t/m tatief
    Tarief = WorksheetFunction.VLookup(Dat, Bron, 3, True)
        'hier wordt het tarief bepaald
End Function

 

[sylvester-ponte]

deze werkt ook met ongesorteerde bron:

 

[Marccuriel]

Hoi Sylvester,
Heel erg bedankt voor je hulp. Ik ga er morgen mee aan de slag.

Het data bestand (waar ik een tarief bij moet zoeken) is keurig gesorteerd op datum (dus de Id's staan niet bij elkaar). Het bron bestand kan ik wel standaard op ID en daarna op datum sorteren.

Dank alvast!

 

[pixcel]

Was even benieuwd of dit type probleem eenvoudig met PowerPivot op te lossen zou zijn. Dat viel nog tegen, maar bijgevoegd het resultaat. Werkt niet in 2007, en in 2010 maar matig (dan heb je de powerpivot addin nodig om de gegevens te vernieuwen). In 2013 is een refresh van de draaitabel voldoende om alles te vernieuwen, dat werkt dus prettig.