Excel: welke records horen bij elkaar (meerdere criteria)

[Yupp]

Mijn vraag
Stel ik heb een Excel bestand met 1000 rijen (records). Elk record heeft een id, een datum, een tijd (uur, minuten, seconden), een locatie (wegnr, hectometer (km met 1 decimaal, bv 34,5) en tot slot een waardering (bv A, B, C, D of E).

Nu wil ik voor elk record met waardering A zoeken naar andere records in dit bestand met:
- een tijd die maximaal X minuten binnen (voor of na) de tijd van het te onderzoeken record liggen, en
- op dezelfde weg en op maximaal 5km van de locatie van het te onderzoeken record liggen, en
- een willekeurige waardering hebben (dus ook waardering A doet mee)

Dus voor elk record moet gekeken worden naar alle andere records en bepaald worden of ze aan de voorwaarden voldoen van het te onderzoeken record:
- voor record met id 1: welke van de andere 999 records voldoen aan de voorwaarden
- voor record met id 2: welke van de andere 999 records voldoen aan de voorwaarden

De records zijn gesorteerd op datum tijd. In de praktijk voldoet een record aan de voor waarden als het ongeveer maximaal 25 records voor of na (bestand gesorteerd op datum tijd) het te onderzoeken record ligt.

Relevante software en hardware die ik gebruik
Excel 365

Wat ik al gevonden of geprobeerd heb
Ik heb een opzet waarbij ik een record kan aanwijzen en Excel vervolgens kijkt welke andere records erbij horen (aan de voorwaarden voldoen). Maar hiermee moet ik de records 1 voor 1 bekijken.
...

[Orion84]

Misschien goed om die opzet dan ook even verder toe te lichten / te delen en verder uit te leggen wat daar niet aan voldoet / wat je wel verwacht?

Je filter criteria zijn denk ik wel te begrijpen, maar wat je nu precies voor output verwacht? Hoe wil je dat die relaties weergegeven worden?

[Djordjo]

Yupp schreef op donderdag 17 november 2022 @ 14:41:
In de praktijk voldoet een record aan de voor waarden als het ongeveer maximaal 25 records voor of na (bestand gesorteerd op datum tijd) het te onderzoeken record ligt.

Dan bouw je een matrix van (#A-records) bij 50 en toont in elke cel een id, indien het aan de voorwaarden voldoet?

[Roozzz]

Klinkt als iets wat je met een database wilt oplossen, niet in excel. Zeker als je dit "automagisch" voor tal van records wilt ophoesten.

[dix-neuf]

Yupp schreef op donderdag 17 november 2022 @ 14:41:
Wat ik al gevonden of geprobeerd heb
Ik heb een opzet waarbij ik een record kan aanwijzen en Excel vervolgens kijkt welke andere records erbij horen (aan de voorwaarden voldoen). Maar hiermee moet ik de records 1 voor 1 bekijken.

Hoe ziet die 'opzet' er uit ?

[GRDavies75]

Hoewel PowerQuery beter geschikt is om data te preppen en dat te voeren aan tools voor data analyse of data visualisatie, zou ik voor dit vraagstuk hier Powerquery gebruiken. Het gaat ook maar om 1000 records, niet heel groot.

Zodat je je oorspronkelijke tabel verrijkt met een extra veld/kolom met daarin de tabel van gerelateerde records als basis. Iets in de trant van en custom functie die je een tabel voert (jouw originele tabel) en het id van het record waar je de gerelateerde records van zoekt en dan de gefilterde tabel teruggeeft.

De volgende stap zou dan zijn om deze tabel uit te klappen en kom je uit op de hamvraag die al 2 keer eerder is gesteld, hoe ziet de uiteindelijke opzet eruit? Wat je of verder in PQ uitwerkt of het laadt richting Excel en daar verder gaat richting je uiteindelijke opzet.

[ Voor 3% gewijzigd door GRDavies75 op 18-11-2022 14:06 ]

[Yupp]

Mijn opzet is als volgt.

1. ik maak een extra kolom (kolom a) voor alle data waar ik een rij (record) "actief" kan maken (deze rij ga ik onderzoeken) door er een x voor te zetten
2. In een paar extra rijen boven de data geef ik het actieve record weer: ID, tijd en locatie. Met een formule (verticaal de x in kolom A zoeken, geef tijd en locatie weer) maak ik de velden Tijd-min (tijd van het record minus x minuten), Tijd-max (tijd record plus x minuten, Locatie-min en Locatie-max (locatie van actief record minus en plus 5km)
3. Ik maak een paar kolommen achter de data met criteria testers die voor elke rij (record) test of de tijd van dat record groter is dan Tijd-min, kleiner dan Tijd-max ... hetzelfde idee voor locatie: groter dan Locatie-min en kleiner dan Locatie-max
4. Als aan alle 4 deze voorwaarden is voldaan dan hoort een rij bij het actieve (te onderzoeken) record

Op deze manier kan ik binnen mijn 1000 records heel snel controleren of ze bij een specifiek (te onderzoeken) record horen.

Wil ik van elk record van die 1000 records weten welke andere records erbij horen (voldoen aan de 4 criteria: >Tijdmin <Tijdmax >Locmin <Loxmax) dan moet ik 1000 keer de x voor een rijd zetten om deze actief (onderzoekbaar) te maken.

Hoe kan ik dit doen zonder 1000 keer een handeling uit te voeren?

Linkje naar Excelbestand met 3 groene invoervakken: in de eerste groene cel geef je aan welk record id je gaat onderzoek (ipv de eerder beschreven methode met x)

[ Voor 9% gewijzigd door Yupp op 19-11-2022 12:47 ]

[dix-neuf]

Yupp schreef op zaterdag 19 november 2022 @ 11:41:
Wil ik van elk record van die 1000 records weten welke andere records erbij horen (voldoen aan de 4 criteria: >Tijdmin <Tijdmax >Locmin <Loxmax) dan moet ik 1000 keer de x voor een rij zetten om deze actief (onderzoekbaar) te maken.

Ja, en dat kost tijd, heel veel tijd. Ik denk niet dat je dat voor alle 1000 (of meer?) rijen al gedaan hebt. Hieronder een oplossing die dat in enkele seconden doet (die heeft ook veel tijd gekost hoor, zéér veel tijd).
Zorg dat je gegevens in Blad1 precies komen te staan zoals in de afbeelding hieronder. Die afbeelding geeft maar 12 rijen met gegevens weer, in werkelijkheid moeten er dat 1000 of meer worden als ik je goed heb begrepen. De nummering in kolom A vanaf A6 is eenvoudig: voer in A6 en A7 de cijfers 1 en 2 in, selecteer die twee cellen en kopieer ze met de vulgreep naar beneden tot zover als nodig.


Centreer in alle kolommen (tot 25 kolommen na kolom C) de tekst horizontaal. In jouw voorbeeld werd het resultaat (voor slechts 1 rij) vertikaal weergegeven, ik heb gekozen voor horizontaal omdat vertikaal vanaf kolom D 1000 kolommen met gegevens zou opleveren, dat is niet erg praktisch.

Toets ALT-F11 om naar de vba-editor te gaan en kies bovenaan in het menu: Invoegen - Module.
Plaats in de module onderstaande code:

code:

Sub Berekening()
Dim dist As Double, tim As Date, ty As Date, x As Integer
Dim lr As Integer, tmax As Date, tmin As Date, tx As Date
Dim locx As Double, locmax As Double, locmin As Double, y As Integer
With Sheets("Blad1")
Application.ScreenUpdating = False
.Columns("d:az").ClearContents
tim = .Cells(3, 2).Value: dist = .Cells(3, 3).Value
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
For x = 6 To lr
tx = .Cells(x, 2).Value: locx = .Cells(x, 3).Value
tmin = tx - tim: tmax = tx + tim
locmin = locx - dist: locmax = locx + dist
For y = 6 To lr
ty = .Cells(y, 2).Value: locy = .Cells(y, 3).Value
If WorksheetFunction.And(ty >= tmin, ty <= tmax, locy >= locmin, locy <= locmax) Then
fcol = .Cells(y, .Columns.Count).End(xlToLeft).Column + 1
.Cells(y, fcol) = .Cells(x, 1).Value
End If
Next y
Next x
Application.ScreenUpdating = True
End With
End Sub

Klik daarna op het kruisje rechtsboven om terug te keren naar het werkblad.
Voer in B3 en C3 de gewenste gegevens in en geef daarna opdracht om de macro uit te voeren.
Je kunt daarvoor evt. een knop in de 'werkbalk snelle toegang' plaatsen of in het blad zelf.
Binnen enkele seconden wordt het resultaat (voor 1000 of meer rijen) weergegeven.
Succes ermee!
NB: De 'waardering' van de rijen (A t/m E) waarover je het in je eerste bericht hebt, was in je bestand niet te vinden.

[ Voor 3% gewijzigd door dix-neuf op 22-11-2022 06:40 ]

[GRDavies75]

Yupp schreef op zaterdag 19 november 2022 @ 11:41:
Mijn opzet is als volgt.

1. ik maak een extra kolom (kolom a) voor alle data waar ik een rij (record) "actief" kan maken (deze rij ga ik onderzoeken) door er een x voor te zetten
2. In een paar extra rijen boven de data geef ik het actieve record weer: ID, tijd en locatie. Met een formule (verticaal de x in kolom A zoeken, geef tijd en locatie weer) maak ik de velden Tijd-min (tijd van het record minus x minuten), Tijd-max (tijd record plus x minuten, Locatie-min en Locatie-max (locatie van actief record minus en plus 5km)
3. Ik maak een paar kolommen achter de data met criteria testers die voor elke rij (record) test of de tijd van dat record groter is dan Tijd-min, kleiner dan Tijd-max ... hetzelfde idee voor locatie: groter dan Locatie-min en kleiner dan Locatie-max
4. Als aan alle 4 deze voorwaarden is voldaan dan hoort een rij bij het actieve (te onderzoeken) record

Op deze manier kan ik binnen mijn 1000 records heel snel controleren of ze bij een specifiek (te onderzoeken) record horen.

Wil ik van elk record van die 1000 records weten welke andere records erbij horen (voldoen aan de 4 criteria: >Tijdmin <Tijdmax >Locmin <Loxmax) dan moet ik 1000 keer de x voor een rijd zetten om deze actief (onderzoekbaar) te maken.

Hoe kan ik dit doen zonder 1000 keer een handeling uit te voeren?

Linkje naar Excelbestand met 3 groene invoervakken: in de eerste groene cel geef je aan welk record id je gaat onderzoek (ipv de eerder beschreven methode met x)

Zie: https://imgur.com/a/WxQv9rC
Je voorbeeld heeft maar 94 records, maar zie hier de opzet die geen moeite heeft met de verwante records aan te leveren. Als je in je voorbeeld je complete dataset zou zetten, dan kan ik die ook verwerken. En gezien het rekenkundig werk wat verzet moet worden zou het me verbazen als het langer dan een minuut zou duren.

[Yupp]

dix-neuf schreef op maandag 21 november 2022 @ 19:59:
[...]
.... heeft ook veel tijd gekost hoor, zéér veel tijd)...

Merci beaucoup 19 ;-)

Loop na uitvoeren macro tegen dit probleempje aan:

[Yupp]

GRDavies75 schreef op dinsdag 22 november 2022 @ 09:57:
[...]
Zie: https://imgur.com/a/WxQv9rC

Dank voor je reactie GRDavies75
Op basis van het plaatje kan ik dit niet meteen uitvoeren. Ik moet me gaan verdiepen in de Excel powerqueries... ga ik doen... Tips voor een YTkanaal of online les?

[ Voor 10% gewijzigd door Yupp op 22-11-2022 16:11 ]

[dix-neuf]

Yupp schreef op dinsdag 22 november 2022 @ 16:08:
Loop na uitvoeren macro tegen dit probleempje aan
[Afbeelding]

Dat is vreemd. Ik heb die macro zeker wel 50 x laten lopen zonder enig probleem.
Als je in die messagebox op 'Foutopsporing' klikt, welke regel wordt dan als fout aangeduid?
Je hebt toch geen rijen of kolommen beveiligd?
Met welke versie van Excel werk je?

[ Voor 9% gewijzigd door dix-neuf op 22-11-2022 16:24 ]

[GRDavies75]

Yupp schreef op dinsdag 22 november 2022 @ 16:10:
[...]

Dank voor je reactie GRDavies75
Op basis van het plaatje kan ik dit niet meteen uitvoeren. Ik moet me gaan verdiepen in de Excel powerqueries... ga ik doen... Tips voor een YTkanaal of online les?

Met Full price is het een behoorlijke duit, maar Udemy strooit regelmatig met aanbiedingen en persoonlijk is het één van de beste 25 euro investeringen geweest sinds recente tijden, maar ik ben uitermate tevreden over de vele content aangeboden in:

Excel Power Query Beginner to Advanced (including M) van Leila Gharani

Ik vind Excel een machtig mooie tool met veel out of the box, maar d.m.v. Powerquery wordt het een nog krachtigere tool. En met behulp van die cursus heb je de mogelijkheid om een goede basis te krijgen in PQ.

[ Voor 5% gewijzigd door GRDavies75 op 22-11-2022 16:26 ]

[Yupp]

dix-neuf schreef op dinsdag 22 november 2022 @ 16:18:
[...]... Als je in die messagebox op 'Foutopsporing' klikt, welke regel wordt dan als fout aangeduid?
Je hebt toch geen rijen of kolommen beveiligd?
Met welke versie van Excel werk je?

Ik heb niets beveiligd en werk met de Office 365 (Versie 2210 build 15726.20202)

Linkje naar mijn bestand

[dix-neuf]

Yupp schreef op dinsdag 22 november 2022 @ 19:31:
Linkje naar mijn bestand

Dowloaden van je bestand is niet mogelijk (met 3 browsers geprobeerd).
Er verschijnt deze 2-regelige melding:
Unautorized
Error 401

[Yupp]

dix-neuf schreef op dinsdag 22 november 2022 @ 20:47:
[...]

Dowloaden van je bestand is niet mogelijk (met 3 browsers geprobeerd).
Er verschijnt deze 2-regelige melding:
Unautorized
Error 401

Please try again:
Linkje naar mijn bestand

[dix-neuf]

Het spijt me het te moeten zeggen, maar je hebt toch niet gedaan wat ik zo nadrukkelijk geschreven had. Ik schreef (o.a.) en vetgedrukt: "Zorg dat je gegevens in Blad1 precies komen te staan zoals in de afbeelding...".
In jouw Blad1 kun je de macro niet uitvoeren zoals het blad nu is ingedeeld, dat had je waarschijnlijk wel gezien en daarom heb je een kopie gemaakt en die aangepast. Maar de naam van die kopie is (uiteraard) niet "Blad1" of "Blad2", maar "Blad1 (2)". Pas die laatste naam aan en noem het Blad2 (geen spatie tussen Blad en 2). En natuurlijk moet je dan ook in de macro in regel 5 de tekst With Sheets("Blad1") wijzigen in: With Sheets("Blad2"). Dan zul je zien dat de macro werkt als een zonnetje in Blad2. Doe a.u.b. geen andere aanpassingen aan het blad en aan de macro alvorens je de werking van de macro hebt gezien en laat dat a.u.b. hier even weten.

[Yupp]

dix-neuf schreef op dinsdag 22 november 2022 @ 21:36:
Het spijt me het te moeten zeggen, maar je hebt toch niet gedaan wat ik zo nadrukkelijk geschreven had. Ik schreef (o.a.) en vetgedrukt: "Zorg dat je gegevens in Blad1 precies komen te staan zoals in de afbeelding...".
In jouw Blad1 kun je de macro niet uitvoeren zoals het blad nu is ingedeeld, dat had je waarschijnlijk wel gezien en daarom heb je een kopie gemaakt en die aangepast. Maar de naam van die kopie is (uiteraard) niet "Blad1" of "Blad2", maar "Blad1 (2)". Pas die laatste naam aan en noem het Blad2 (geen spatie tussen Blad en 2). En natuurlijk moet je dan ook in de macro in regel 5 de tekst With Sheets("Blad1") wijzigen in: With Sheets("Blad2"). Dan zul je zien dat de macro werkt als een zonnetje in Blad2. Doe a.u.b. geen andere aanpassingen aan het blad en aan de macro alvorens je de werking van de macro hebt gezien en laat dat a.u.b. hier even weten.

Ik vind dat je dat dan erg slecht hebt uitgelegd hoor... hahaha.. Nee, je hebt gelijk. Blad1 verwijderd, ander hernoemd naar Blad1 .. .en hatsjikidee... werkt als een speer. Hartelijk dank, hier kan ik prima mee uit de voeten. Desondanks ga ik me ook verdiepen in Excel power query.

Heel hartelijk dank voor jullie tijd en moeite!!!!!!!!!!!!!