Excel verticaal zoeken bij meerdere waardes

[Rens80]

Hoi,

Ik heb een probleem waar ik niet uit kom.

Ik probeer aan de hand van de gegevens in de kolommen D, E, F de historie van een voetballer volledig uit te werken. In kolom M moet de juiste datum uit kolom E komen te staan. En in kolom N uit kolom F. Echter doordat ik dezelfde waarde heb krijg ik in M9 de datum van de eerste waarde. Dit moet die van de tweede zijn.

Een clubnaam kan in de historie ook meer dan twee keer voorkomen. Dus bij drie keer moet die de derde datum pakken

Weet iemand hoe ik dit op kan lossen?

 

[HSV]

Invoeren als matrix, dan pas doorvoeren naar onderen en naar rechts.

=ALS(L5="";"";INDEX(E$5:E$17;KLEINSTE(ALS($D$5:$D$17=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5))))

 

[Rens80]

Beste HSV,

Dank voor de hulp. Op het bestand wat ik had geupload werkt het perfect. Echter als de carriere verder invul kom ik op het volgende probleem ( zie de bijlage ). Heb je hier misschien een oplossing voor?

Bij voorbaat dank!

 

[HSV]

=als(l5="";"";als.fout(index(e$5:e$17;kleinste(als($d$5:$d$17=$l5;rij($1:$13));aantal.als($l$4:$l5;$l5)));""))

 

[Rens80]

Ha HSV,

Mijn excuses, ik heb niet goed uitgelegd wat de bedoeling is. In de bijlage in zwart de uitkomst van jouw formule. In rood heb ik handmatig ingevoegd wat het zou moeten zijn.

Als er geen einddatum is moet hij automatisch aanvullen tot aan vandaag

Hopelijk heb je nog tijd om hier nog mee naar te kijken

Wederom alvast bedankt! Ik ben al een heel eind op weg geholpen!

 

[HSV]

Dit moet in de buurt komen.
Misschien een klein foutje aan jouw kant?

=ALS($L5="";"";ALS(OF(AANTAL.ALS($L$4:$L5;$L5)>AANTAL.ALS($D$5:$D$19;$L5);NIET(ISGETAL(INDEX($F$5:$F$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5))))));J5;ALS.FOUT(INDEX(E$5:E$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5)));"")))

 

[Rens80]

Beste Harry,

Ik heb de formule ingegeven( zie de bijlage ), maar heb nog drie data die niet juist zijn. Deze zijn niet juist, want de data moeten altijd binnen het begin en einde van het betreffende seizoen in dezelfde rij vallen. Daarnaast moeten alle data's elkaar opvolgen.

Ik probeer zelf mee te denken waar de oplossing ligt, maar de matrix formules gaan mijn pet te boven.

Hopelijk is er een oplossing.... Bedankt voor alle moeite tot nu toe!!

 

[sylvester-ponte]

met vba ( hij kijkt per seizoen dus er kunnen eigenlijk wat rijtjes verdwijnen )

 

[Rens80]

Beste Harry,

Ik heb nu zelf deze formule gemaakt

=ALS($L5="";"";ALS(ALS(OF(AANTAL.ALS($L$4:$L5;$L5)>AANTAL.ALS($D$5:$D$19;$L5);NIET(ISGETAL(INDEX($F$5:$F$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5))))));J5;ALS.FOUT(INDEX(E$5:E$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5)));""))<J5;J5;ALS(OF(AANTAL.ALS($L$4:$L5;$L5)>AANTAL.ALS($D$5:$D$19;$L5);NIET(ISGETAL(INDEX($F$5:$F$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5))))));J5;ALS.FOUT(INDEX(E$5:E$19;KLEINSTE(ALS($D$5:$D$19=$L5;RIJ($1:$13));AANTAL.ALS($L$4:$L5;$L5)));""))))

Die verhelpt een deel van de problemen. Bij het evalueren zie ik volgens mij dat het probleem nu is dat een clubnaam in de ene kolom vaker voorkomt dan in de andere kolom, waardoor de formule niet de juist uitkomst geeft. De formule zou eigenlijk een reeks van dezelfde namen achter elkaar als 1 moeten zien.

Heb jij een idee of dat mogelijk is?

 

[Rens80]

Beste Sylvester,

Dank voor je hulp. De uitkomst moet echter zijn dat de clubs in volgorde onder elkaar komen te staan.

Groeten,

Rens

 

[sylvester-ponte]

klein beetje aangepast:

 

[sylvester-ponte]

of zo iets (nog een beetje verder uitgesplitst)

 

[Rens80]

Ha Sylvester,

Dat ziet er goed uit. Dank voor de hulp.

Is het niet mogelijk om de datums in een aparte kolom te zetten?

gr

Rens

 

[sylvester-ponte]

zie post 12

 

[Rens80]

Ha Sylvester,

Dit ziet er super uit! thnx!

als ik deze wil kopieren in een ander groter bestand moet ik dan iets in de VBA aanpassen?

Als de cellen anders zijn pas ik de matrixformule daarop aan. Moet ik nog ergens anders aan denken? Moet ik de macro nog activeren?

Gr

Rens

 

[sylvester-ponte]

macro's moeten toegestaan zijn.
de formule zie je staan als je op alt f11 drukt.

je kan ook je originele bestand naar deze kopiëren dan werkt de formule al.
anders moet je deze module1 naar je orginele bestand slepen.
verder kun je deze formule als iedere excel formule gebruiken.


eventueel kun je het andere bestand sturen .
dan kan ik of iemand van hier helpen.

 

[Rens80]

Ha Sylvester,

Ik heb een bijlage bijgevoegd

Als ik de formule kopieer in drie kolommen, opsla sla matrixformule en vervolgens naar beneden doortrek krijg ik de uitkomst in de bijlage ( alleen de clubnamen zonder datums ). Volgens mij komt dat omdat de macro niet uitgevoerd wordt. Ik heb macro's aangezet in het vertrouwenscentrum.

Enig idee waarom het niet werkt? Of doe ik iets verkeerd?

gr

Rens

 

[sylvester-ponte]

maak Q:S leeg

zet de formule in Q5
selectreer Q5:S5
ga naar de formulebalk en druk op contr sheft enter
kopieer Q5:S5 naar beneden
doet hij het nu?

 

[Rens80]

Ha Sylvester,

Getest en het werkt

Mijn dank is groot!

Gr,

Rens

 

[Rens80]

Ha Sylvester,

Nog een aanvullende vraag nu ik een andere berekening wil maken.

Ik wil de gegevens van de rijen beginnend bij 9 t/m 22 kopieren naar een andere tabel. Aangezien deze rijnummers verschillend kunnen zijn ben ik aan het zoeken naar een manier om deze data te kunnen kopieren. Niet de gehele rij, maar enkel de cellen uit deze tabel.

Weet jij misschien hoe ik dat kan doen?

Gr

Rens