in 2 kolommen zoeken

[GerV]

Beste forum,

wie weet een slimme formule om te testen of een ingevoerde datum bij een uniek medewerkersnummer aansluit op een eerder ingevoerde datum bij dit medewerkersnummer.

ik heb 7 kolommen. In kolom A staat het mdwnr, kolom B de naam, kolom C functie, kolom d zijn werkpunt, kolom E de datum van aanvraag, kolom F een datum, kolom G een datum.

De mdw mag 2 dagen aanvragen, deze mogen niet aansluitend zijn. Zijn aanvraag wordt vastgelegd op datum aanvraag. Ik heb al een check erin zitten of mdw meer dan 2 dagen aanvraagt (mbv hulpvelden) en indien de aangevraagde datums op dezelfde regel staan in kolom F en G ook een check of ze aansluitend zijn. =ALS($K2>2;"TE VEEL AANVRAGEN";ALS(G2=F2+1;"AANSLUITEND";ALS(F2=G2+1;"AANSLUITEND";
Wat ik zoek is een formule die vanaf de huidige regel terugkijkt of de datums in kolom F en evt G bij het mdwnr uit kolom a aansluitend zijn bij datums die eerder bij deze mdw zijn ingevoerd in kolom F of G.

Hopelijk is eea duidelijk.

GerV

 

[AlexCEL]

Hopelijk is eea duidelijk.

Een representatief Excel-voorbeeldje zou wel helpen.

 

[GerV]

zie de bijlage.

GerV

 

[AlexCEL]

Welke versie van Excel gebruik je? (Excel 365 ja of nee?)

 

[GerV]

Excel 365.

GerV

 

[AlexCEL]

Mooi. Hou je vast, voor H2:

=LET(x;NAAR.KOLOM(FILTER(F$2:$G2;A$2:A2=A2;""));y;SORTEREN(FILTER(x;x>0;""));z;RIJEN(y);ALS(z>2;"TE VEEL AANVRAGEN";ALS(SOM(--((ALS.FOUT(INDEX(y;REEKS(z;;2));0)-ALS.FOUT(INDEX(y;REEKS(z;;1));0))=1));"AANSLUITEND";"")))

Kun je doortrekken naar onder en zou moeten doen wat je vraagt.

Kolommen I t/m K zijn overbodig en kunnen uit je tabel.

 

[GerV]

Je geeft aan dat i tm K overbodig zijn maar in jouw formule staat: ALS(K2>0


En inderdaad als ik de kolommen I tm K verwijder krijg ik geen melding meer.
GerV

 

[AlexCEL]

Kleine modificatie in de formule:

=LET(x;NAAR.KOLOM(FILTER(F$2:$G2;A$2:A2=A2;""));y;SORTEREN(FILTER(x;x>0;""));z;RIJEN(y);ALS(A2<>"";ALS(z>2;"TE VEEL AANVRAGEN";ALS(SOM(--((ALS.FOUT(INDEX(y;REEKS(z;;2));0)-ALS.FOUT(INDEX(y;REEKS(z;;1));0))=1));"AANSLUITEND";""));""))

Nu zou het goed moeten gaan.

 

[GerV]

Bedankt.

Nu gaat het inderdaad goed.

GerV

 

[AlexCEL]

Super :thumb:

Best wel even puzzelen deze, maar dat is leuk...
Kan waarschijnlijk nog wel wat korter ook, is ook wel een sport. Zo kort mogelijke formule maken.
Misschien dat ik nog met een update kom dus.

 

[GerV]

Zoiets zou ik met mijn beperkte excell kennis nooit zelf kunnen verzinnen.

Nogmaals bedankt.

GerV

 

[GerV]

AlexCel,

nog ff een aanvullende vraag:

kan ik dit stukje ALS.NB(VERT.ZOEKEN($F3;DATAblad!$C$2:$D$16;2;0);"")&ALS.NB(VERT.ZOEKEN($G3;DATAblad!$C$2:$D$16;2;0);"") er gewoon achter plakken? Dit is de controle of het op een van de dagen genoemd op het blad Datablad gaat.

Ger

 

[snb]

Is dit niet handiger ?

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Columns(6).Resize(, 2)) Is Nothing Then Cells(1).CurrentRegion.AutoFilter 2, Target.Offset(, -Target.Column + 2)
End Sub

 

[AlexCEL]

Zo bijv.?

=LET(x;NAAR.KOLOM(FILTER(F$2:$G2;A$2:A2=A2;""));y;SORTEREN(FILTER(x;x>0;""));z;RIJEN(y);ALS(A2<>"";ALS(z>2;"TE VEEL AANVRAGEN";ALS(SOM(--((ALS.FOUT(INDEX(y;REEKS(z;;2));0)-ALS.FOUT(INDEX(y;REEKS(z;;1));0))=1));"AANSLUITEND";ALS(SOM((DATAblad!$C$2:$C$15=F2)+(DATAblad!$C$2:$C$15=G2))>0;"FEESTDAG";"")));""))

Wat bij mij wel de vraag oproept:
1. Wat als ik een dag voor een feestdag vrij vraag? Mag dat? Dat is aansluitend en mag nu niet met bovenstaande formule.
2. Telt een feestdag mee in het aantal aanvragen? Nu wel met bovenstaande formule.

 

[GerV]

snb,

bedankt. Maar de mensen die het format invullen schrikken zich een hoedje als ineens de andere namen "weg" zijn.

De meeste invullers van dit format hebben geen weet van filters te zwijgen van filters tgv een macro.

Ik heb de formule van AlexCel uitgebreid met het stukje van de bepaalde dagen. Zover ik kan zien werkt het nu.

=LET(x;NAAR.KOLOM(FILTER(F$2:$G3;A$2:A3=A3;""));y;FILTER(x;x>0;"");z;RIJEN(y);ALS(A3>0;ALS(z>2;"TE VEEL AANVRAGEN";ALS(SOM(--((ALS.FOUT(INDEX(y;REEKS(z;;2));0)-ALS.FOUT(INDEX(y;REEKS(z;;1));0))=1));"AANSLUITEND";""));"")&ALS.NB(VERT.ZOEKEN($G3;'DATAblad'!$C$2:$D$11;2;0);"")&ALS.NB(VERT.ZOEKEN($F3;'DATAblad'!$C$2:$D$11;2;0);""))

GerV

 

[GerV]

AlexCel,

Wat bij mij wel de vraag oproept:
1. Wat als ik een dag voor een feestdag vrij vraag? Mag dat? Dat is aansluitend en mag nu niet met bovenstaande formule.
2. Telt een feestdag mee in het aantal aanvragen? Nu wel met bovenstaande formule.

je mag de dag voor een feestdag vrij vragen. Dit is alleen aansluitend als je ook de 2de dag voor de feestdag vrij vraagt (bijv 23 en 24 dec)
het is de bedoeling dat de invuller nav de melding de dag uit de inschrijving haalt en de aanvrager mededeelt dat de aanvraag niet aan de richtlijnen voldoet.
Dus de dagen die afgekeurd worden zullen eruit gehaald moeten worden door de invuller.


Hierop wordt periodiek gecontroleerd.

GerV

 

[snb]

Dat hoef je die mensen maar eenmaal te tonen.
Waarom zouden ze überhaupt de gegevens van anderen moeten zien ?

 

[GerV]

snb,

nav de correcte inschrijvingen worden de mdw's op de gevraagde dag(en) vrij gemaakt. Daarom moeten alle dagen zichtbaar blijven. Zoals ik al eerder aangaf is het de bedoeling dat foutieve inschrijvingen worden verwijderd en een bericht naar de aanvrager gaat dat zijn inschrijving is afgewezen nav de opmerking uit kolom H.

PS alleen de inschrijver ziet deze data en is ook diegene die de mensen moet vrijmaken.

GerV

 

[snb]

Heb je het bestand wel geopend en getest ?
Je kunt beter 'foutieve' inschrijvingen voorkomen, dan die achteraf verwijderen.

Jouw werkwijze lijkt me voor eenieder een onnodig inefficiënte en teleurstellende procedure.

Draai voor de lol ook eens:

Sub M_snb()
   MsgBox Blad1.Cells.FormatConditions.Count
End Sub