Vertikaal zoeken op datum

[Woffels]

Zie bestand
Op blad Tabel een tabel waarvan ik kolom B dmv verticaal zoeken op datum wil vullen. Tabblad bron bevat de waardes waarin gezocht moet worden. Als de datum matched, dan mag de zoekwaarde ingevuld worden. Maar nu ontbreken er een aantal tijdstippen in de bron (in dit geval op 00:05, 00:27, en 01:00). Vertikaal zoeken komt dan niet met een lege waarde of foutwaarde, maar met de vorige zoekwaarde. Hoe kan dit? Hoe is dit op te lossen? Er zou een lege cel moeten komen of een foutcel mag ook. Een vba oplossing mag ook, maar ik wil ook weten waarom dit zo niet werkt.

 

[HSV]

Je bent vergeten het vierde argument van de functie vert.zoeken in te vullen.

 

[Woffels]

Als ik die invul (onwaar) dan worden alle waardes #N/B

 

[HSV]

Selecteer elke cel een voor een in de eerste kolom en druk F2 en daarna op Enter.

=VERT.ZOEKEN([@[Datum/tijd]];_20582848;2;0)

 

[Woffels]

Uiteindelijk gaan er 1000-en rijen komen, dan is alles apart selecteren niet echt handig. Is er niet een elegantere oplossing?

 

[HSV]

Selecteer A2 en druk Ctrl+Shift+pijltjestoets naar beneden.

Tekst naar kolommen en druk op voltooien.

 

[Woffels]

Dat werkt niet, behalve de eerste is de rest #NB

 

[HSV]

Werkt als een tierelier; hier je bestand retour.

 

[Woffels]

Nu ook bij mij.

 

[snb]

Met VBA:

Sub M_snb()
  sn = Blad15.ListObjects(1).DataBodyRange
  Blad15.ListObjects(1).DataBodyRange = sn

  sn = Blad24.ListObjects(1).DataBodyRange
  Blad24.ListObjects(1).DataBodyRange = sn

  Blad15.ListObjects(1).Name = "T_01"
  Blad24.Cells(2, 2) = "=IFERROR(INDEX(T_01[Waarde],MATCH([@[Datum/tijd]],T_01[Time],0)),AVERAGE($B1,$B3))"
End Sub

 

[Woffels]

En als het helemaal in vba zou moeten en dus vba geen formule laten maken, maar de volledige berekening zelf zou laten maken op de hele rij?

 

[HSV]

Twee suggesties.

Sub hsv()Dim sv, x
 With Application
    sv = Blad15.ListObjects(1).DataBodyRange
    x = Blad24.ListObjects(1).DataBodyRange.Columns(1)
    sv = .Index(sv, .Match(x, .Index(sv, , 1), 0), 2)
  Blad24.ListObjects(1).DataBodyRange.Columns(2) = sv
 End With
End Sub

Sub hsv()
Dim sv, sq, i As Long
With CreateObject("scripting.dictionary")
   sv = Blad15.ListObjects(1).DataBodyRange
   sq = Blad24.ListObjects(1).DataBodyRange
      For i = 1 To UBound(sv)
        .Item(sv(i, 1)) = sv(i, 2)
      Next i
      For i = 1 To UBound(sq)
        sq(i, 2) = .Item(sq(i, 1))
      Next i
  Blad24.ListObjects(1).DataBodyRange = sq
End With
End Sub

 

[Woffels]

Werkt razendsnel, de laatste op een lijst met 500k+ regels

 

[snb]

Als je meetwaarden wil 'normaliseren' naar alle minuten op een dag kan dat in 1 werkblad en volledig in het werkgeheugen zonder werkbladfunkties:

Sub M_snb()
  sn = Blad15.ListObjects(1).DataBodyRange
  sp = [if(column(A:B)=1,int(A2)+row(1:1440)/1440,"")]
  
  For j = 1 To UBound(sn)
    sp(Minute(sn(j, 1)) + 60 * Hour(sn(j, 1)), 2) = sn(j, 2)
  Next
  
  Blad15.Cells(2, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
  Blad15.Columns(4).NumberFormat = "dd-mm-yyyy hh:mm"
End Sub

 

[Woffels]

Die is nog mooier, bedankt

 

[Woffels]

En als het nu om meerdere dagen gaat? Dan komt ie met een foutmelding, de max lijkt 1440 te zijn. In werkelijkheid kan het best wel om maanden gaan.

 

[snb]

Jouw voorbeeld gaat uit van 1 dag.
Een representatief voorbeeld doet wonderen.

1 dag= 24 uur * 60 minuten = 1440 minuten
2 dagen = 2*1440
3 dagen = 3*1440

1 jaar = 365 * 1440 = 525600

Ergo:

Sub M_snb()
  sn = Blad15.ListObjects(1).DataBodyRange
  sp = [if(column(A:B)=1,int(A2) + row(1:525600)/1440,"")]
  
  For j = 1 To UBound(sn)
    sp(Minute(sn(j, 1)) + 60 * Hour(sn(j, 1)) + 1440 * int(sn(j,1) - sn(1,1)), 2) = sn(j, 2)
  Next
  
  Blad15.Cells(2, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
  Blad15.Columns(4).NumberFormat = "dd-mm-yyyy hh:mm"
End Sub

NB. de data in sn moeten dan natuurlijk wel in hetzelfde jaar liggen vanaf $A$2

 

[Woffels]

Ik dacht dat het bestand te groot zou zijn om hier te uploaden, maar hier een kortere versie met meerdere dagen. Je code lijkt vast te lopen bij het overgaan naar de volgende dag in de bron(sn)

 

[snb]

Je bestand is niet consistent:
Tijdstip 01-01-2022 00:00 ontbreekt, terwijl die bij de overige dagen wel aanwezig is.

HJet is nooit verkeerd zelf op zoek te gaan nar de mogelijke oorzaak van een foutmelding.

Zet deze code in de macromodule van werkblad Blad15; niet in een aparte macromodule.

Sub M_snb()
  Application.ScreenUpdating = False
  sn = Blad15.ListObjects(1).DataBodyRange
  sp = [if(column(A:B)=1,int(A2) + (row(1:525600)-1)/1440,"")]
  
  For j = 1 To UBound(sn)
    sp(1 + Minute(sn(j, 1)) + 60 * Hour(sn(j, 1)) + 1440 * DateDiff("d", sn(1, 1), sn(j, 1)), 2) = sn(j, 2)
'   sp(1 + Minute(sn(j, 1)) + 60 * Hour(sn(j, 1)) + 1440 * (Int(sn(j, 1)) - Int(sn(1, 1))), 2) = sn(j, 2)
  Next
  
  Blad15.Cells(2, 4).Resize(UBound(sp), UBound(sp, 2)) = sp
  Blad15.Columns(4).NumberFormat = "dd-mm-yyyy hh:mm"
End Sub

 

[Woffels]

De aangeleverde data is ook niet altijd consistent en kan minuten missen, daar gaat het nu precies om.