index en vergelijken meerdere resultaten

[jowey]

Hallo,

ik heb al eerder een oplossing hiervoor gezien echter begrijp ik m niet helemaal.

Bijgaand 2 bestanden. Een planning en een bestand waaruit de planning de namen haalt.
Omdat op sommige plekken meerdere mensen werken en het uitzendbureau dit niet kan/wil aanpassen ben ik op zoek naar een passende oplossing.


Het gaat om het gedeelte dock 2 waar al een formule instaat die werkt. Nu is het de bedoeling dat bij elke plek een andere naam komt aan de hand
van het 2e bestand.

Dus in het voorbeeld kom dock 4 keer voor. Daar zijn 4 verschillende namen voor. Hoe pas ik de huidige formule aan opdat hij dit goed wegschrijft?

Alvast bedankt.Bekijk bijlage 1 week 28 maandag vroeg.xlsxBekijk bijlage Presentielijst week 28.csv.xlsx

 

[AlexCEL]

Je voorbeelden zijn enorm irritant om mee te werken door de vele externe verwijzingen die erin staan, zoals:
H:\2018\week 2\[Presentielijst week 28.csv]Blad1'!$J:$J
'[Presentielijst week 28]Blad1'!$A:$A
C:\Temp\2018\week 2\[Planning 2017.test.xlsm]afblijven'!$U$2

Die heb ik dus allemaal verwijderd. Ik werk ook met een extra tabblad "presentielijst" zodat ik met maar één bestand te maken heb. Jij mag daar eventueel weer een externe verwijzing van maken, het gaat mij om het demonstreren van een mogelijke werkwijze. Bestudeer de formules in bijlage maar eens. Is dit wat je zoekt?

Let op: het zijn matrixfuncties, d.w.z. afsluiten met Control+Shift+Enter na invoeren/wijzigen.

 

[jowey]

excuses daarvoor. Je hebt idd gelijk. Had beter in tabblad kunnen zetten voor het gemak.

Misschien een beetje veel gevraagd maar zou je mij kunnen uitleggen hoe de formule opgebouwd is.

=INDEX('Presentielijst wk28'!$D:$D;KLEINSTE(ALS(LINKS('Presentielijst wk28'!$M$2:$M$5;4)="Dock";RIJ('Presentielijst wk28'!$M$2:$M$5);9^99);RIJ()-21)

Argument 1 'Presentielijst wk28'!$D:$D; (dit is natuurlijk duidelijk)
Argument 2 KLEINSTE(ALS(LINKS('Presentielijst wk28'!$M$2:$M$5;4)="Dock";RIJ('Presentielijst wk28'!$M$2:$M$5);9^99);RIJ()-21)

LINKS begrijp ik niet helemaal. Zo ver ik het kan zien test je hier of het aantal tekens gelijk is aan 4 en of dat gelijk is aan dock. Waarom test je aantal tekens? Als waarde waar is dan geeft hij het rijnummer waarom is waarde bij onwaar zo een hoog getal?
Als ik hier LINKS('Presentielijst wk28'!$M$2:$M$5;4)="Dock weghaal en ingeef "Dock"=B22 dan werkt de formule gewoon

KLEINSTE kan ik mij niet voor de geest halen wat nu de matrix is waarin hij kijkt. Daarnaast begrijp ik k niet. Waarom is die -21?

De hoop is dat ik na uitleg hierover in de toekomst dergelijke formules zelf kan opstellen.

Nogmaals excuses voor onduidelijke voorbeeld en bij voorbaat dank voor je hulp.

Gr Jowey

 

[jowey]

Hey Alex,

Ik heb de formule even getest.

Hij mist helaas nog 1 belangrijk onderdeel.
Hij moet namelijk cel B4 meenemen in de vergelijking omdat de presentielijst die opgestuurd word een weeklijst betreft.
Maw dezelfde namen komen meerdere keren terug. Dat stond niet in het voorbeeld omdat ik dacht dat mijn formule al goed was en er maar
een kleine toevoeging bij moest.
Kan dit nog toegevoegd worden of moet het dan via een totaal andere formule?

Alvast bedankt

Bekijk bijlage bijna.correct.xlsx

 

[AlexCEL]

Zie bijlage: dit naar wens?

De formule wordt wat ingewikkelder bij meerdere voorwaarden...

=ALS.FOUT(INDEX('Presentielijst wk28'!$J:$J;KLEINSTE(ALS(LINKS('Presentielijst wk28'!$M$2:$M$17;4)="Dock";1;9^99)*ALS('Presentielijst wk28'!$A$2:$A$17=$B$4;1;9^99)*RIJ($M$2:$M$17);RIJ()-21));"UZK")

Enige toelichting:

• Dit is een matrixfunctie. Matrixfuncties sluit je niet af met Enter maar met Control+Shift+Enter. Resultaat is dat er niet 1 waarde uit de formule komt maar een rij met waardes (een “matrix”). In dit geval een matrix met rij-nummers die aan de voorwaarden (“Dock”-werker en correcte datum) voldoen, afgewisseld met HELE grote getallen voor rijen die niet voldoen.
• De voorwaarden worden gechecked met de 2 ALS-functies. Allereerst wordt met ALS(LINKS(..;4) gechecked of de eerste 4 letters overeenkomen met “Dock”. Dit heb ik gedaan omdat er 2 functies zijn: "Dock" en "Dock Allround". De ALS() functie zet een 1 in de rij als er overeenkomst is en een heel groot getal als dit niet zo is. Resultaat is per rij een 1 of een groot getal.
• De andere ALS functie werkt net zo, maar kijkt of de datum overeenkomt. Ook hier is het resultaat per rij een 1 of een groot getal.
• Als je deze twee met elkaar vermenigvuldigd krijgt je een nieuwe rij getallen. Door met het RIJ-nummer te vermenigvuldigen heb ik nu een lijst gekregen met rij-nummers die overeenkomen afgewisseld met HELE grote getallen.
• Middels de functie KLEINSTE wordt nu de kleinste (1e) waarde uit de rij. Omdat de functie op rij 22 begint, gebruik ik als argument hiervoor RIJ()-21. Bij naar beneden doortrekken van de formule krijg ik daarmee een reeks 1,2,3,4,5 etc. In de volgende rij (23) bepalen we dan de op 2 na kleinste waarde uit de rij (de 2e overeenkomst), op rij 24 de op 3 na kleinste (3e overeenkomst etc.) etc etc.
• De INDEX functie zoekt de bijbehorende cel op. Dit gaat goed totdat bijvoorbeeld de op 4 na kleinste waarde een HEEL groot getal is. Dan geeft de INDEX functie een fout want cel J9999999999999999 bestaat niet… Middels de ALS.FOUT komt er dan “UZK” te staan (of een andere gewenste melding).

Hopelijk helpt deze toelichting bij het begrijpen van de formule.

 

[jowey]

Helemaal super. Thnx.

Door jouw uitleg snap ik nu ook wat er gebeurt. :thumb: