Op zoek naar formule, index en match

[ongenae]

Hallo iedereen

Ik ben al een aantal dagen aan het worstelen met een vraagstuk.
In feite komt het hier op neer; ik heb een Excel tabel:

[table="width: 500"]
[tr]
[td]PLAAT[/td]
[td]BESTUURDER[/td]
[td]IN GEBRUIK VAN[/td]
[td]TOT EN MET[/td]
[/tr]
[tr]
[td]1ABC100[/td]
[td]JAN[/td]
[td]01/01/2000[/td]
[td]01/12/2015[/td]
[/tr]
[tr]
[td]1ABC100[/td]
[td]POL[/td]
[td]02/12/2015[/td]
[td][/td]
[/tr]
[/table]

Als ik bijvoorbeeld een verkeersovertreding heb op plaat 1ABC100 op datum van 01/11/2015 dan wil ik zoeken wie de bestuurder was op dat ogenblik.

Het menselijk brein zegt onmiddellijk JAN
Via sql raak ik er door

Loop door records waar nummerplaat = 1ABC100
Controleer voor ieder record of boetedatum >= in gebruik van datum en <= tot en met datum
Indien 1 record gevonden dan is het ok

Wie kan mij helpen hoe ik dit best in Excel vertaal, zonder VBA.

Alvast bedankt

 

[popipipo]

Een excel voorbeeldbestandje met een stuk of 10 regels zal enorm gewaardeerd worden.

 

[ongenae]

Een voorbeeld kan je downloaden via deze link

 

[VenA]

Je kan het bestand gewoon hier plaatsen. Hoeven we geen mogelijk gevaarlijk linkje aan te klikken.

Zo dus. Je hebt wel een bijzonder schrikkeljaar. 31-11-2015? dat moet je wel aanpassen naar een bestaande datum anders werkt het niet.

=INDEX($B$2:$B$10;SUMPRODUCT(($A$2:$A$10=I5)*($C$2:$C$10<=J5)*(($D$2:$D$10>=J5)+($D$2:$D$10=""))*ROW($1:$9)))

 

[ongenae]

Bedankt voor het voorbeeld.

Echter heb ik nog twee vraagjes:

1) als ik datum bv 10/12/2015 neem voor 1ABC100 dan krijg ik een naam van een andere plaat
2) als ik de rijen verhoog naar meer dan 9 krijg ik een n/a foutmelding (*ROW($1:$10))

Vermoed dat we gewoon vastlopen omdat dit eerder database query's zijn en dus niet echt geschikt zijn voor Excel.

 

[VenA]

1) Dan krijg je POL wat klopt.
2) Je moet het bereik even groot houden anders kan je inderdaad #N/A krijgen. $B$2:$B$10 bestaat uit 9 rijen.
3) Staat er los van.

 

[ongenae]

Ben voorlopig toch verdergegaan met VBA, dit leek mij op dit ogenblik het eenvoudigst.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim KeyCells As Range
    Dim BoetePlaat As String
    Dim Bestuurder As String
    Dim c As Range
    Dim BoeteDatum As Date
    Dim BestuurderVanDatum As Date
    Dim BestuurderTotDatum As Date
    

   ' De variabele KeyCells bevat de cellen die gewijzigd kunnen worden
   ' en die een actie zullen uitlokken
    Set KeyCells = Range("C:C")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

        ' Indien de cel gewijzigd is doe volgende
        
        ' ## 1 ## Kijk of cel leeg is
        If IsEmpty(Range(Target.Address)) = False Then
        
        ' ## 2 ## Haal nummerplaat en datum van de boete op
            BoetePlaat = Range(Target.Address).Value
            BoeteDatum = Range("F" & Target.Row).Value '26/01/2018 bv
        
        ' ## 3 ## Zoek bestuurder horende bij de plaat op dat ogenblik
        
                ' loop door alle gebruik records
                ' Zoek laatse rij op van import
                lastrowGEBRUIK = ThisWorkbook.Worksheets("Gebruik").Cells(ThisWorkbook.Worksheets("Gebruik").Rows.Count, "D").End(xlUp).Row
                              
                ' Loop door gebruik range
                For Each c In ThisWorkbook.Worksheets("Gebruik").Range("D1:D" & lastrowGEBRUIK)
                
                ' Als boeteplaat is gevonden in lijst
                
                    If c.Value = BoetePlaat Then
                        
                        ' Als datum boete tussen gebruik range valt dan
                    
                        BestuurderVanDatum = ThisWorkbook.Worksheets("Gebruik").Range("F" & c.Row).Value '01/01/2000 bv
                        BestuurderTotDatum = ThisWorkbook.Worksheets("Gebruik").Range("G" & c.Row).Value '01/12/2015 bv
                    
                        ' Controleer of BestuurderTotDatum leeg is (leeg = nog steeds in gebruik)
                        If BestuurderTotDatum = 0 Then
                        BestuurderTotDatum = Date
                        Else
                        End If
                                        
                        If BoeteDatum >= BestuurderVanDatum Then
                    
                            If BoeteDatum <= BestuurderTotDatum Then
                                ' indien gevonden haal bestuurder code op
                                Bestuurder = ThisWorkbook.Worksheets("Gebruik").Range("E" & c.Row).Value
                                MsgBox ("Bestuurder horende bij boeteplaat " & BoetePlaat & " is " & Bestuurder)
                            Else
                            End If

                        Else
                        End If
                                                           
                    End If
            
                Next c
        
        Else
        
        'Cell is blank
        
        End If
        
    End If
    
End Sub

 

[VenA]

Waarom zo'n ingewikkelde code als het gewoon met een formule kan?
De code kan een heel stuk eenvoudiger.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 11 And Target.Row > 4 And Target.Offset(, -2).Value <> "" Then
    ar = Cells(1).CurrentRegion
    For j = 2 To UBound(ar)
      If ar(j, 1) = Target.Offset(, -2) Then
        If ar(j, 3) <= Target.Offset(, -1) And (ar(j, 4) >= Target.Offset(, -1)) + (ar(j, 4) = "") Then
          MsgBox ar(j, 2)
          Exit For
        End If
      End If
    Next j
  End If
End Sub

 

[ongenae]

Kan inderdaad eenvoudiger, probleem is dat de werkelijke Excel stukken complexer is dan het voorbeeld bestand.

 

[VenA]

Dus we worden hier met zijn allen het bos ingestuurd met een vraag waar toch geen oplossing voor te verzinnen is vanwege gebrekkige vraagstelling en het ontbreken van een fatsoenlijk voorbeeldbestand?