Opgelost rang met voorwaarden

[Miertjee]

In het bijgevoegde bestand wil ik sorteren op de kolom doorstroming (geel). De rang in die kolom is gebaseerd op de laatste kolom punten (blauw). In die kolom zitten echter dubbele waarden. Er zijn 3 regels met 60. Dat mag, dat is geen probleem. Het probleem is dat de regel met 60, met de hoogste waarde in de kolom totaalscore (roze), in de kolom doorstroming de hoogste rang moet hebben. Dus daar wil ik niet 3 kinderen op plek 6 hebben, maar gewoon plek 6, 7 en 8. Ze staan nu dan ook op de verkeerde volgorde. Ik wil dus een rang met voorwaarden, maar hoe doe je dat?

Ik kan de rang niet baseren op de kolom totaalscore (roze)want het komt wel voor dat een kind in de roze kolom op plek 6 zou staan, maar in de blauwe kolom op plek 7 en de doorstroming wordt bepaald op de blauwe kolom.
Is het een beetje duidelijk?

 

[AHulpje]

Selecteer het gebied A5 t/m M25
Sorteren en filteren
Aangepast sorteren
Sorteren op kolom M van groot naar klein
Sorteren op kolom L van groot naar klein
OK

Is dat wat je wilt?

 

[Miertjee]

Nee, niet helemaal. Ik wil dat hij in de eerste kolom de rang anders neerzet, met rang 6, rang 7 en rang 8 en niet 3x 6.

 

[AHulpje]

Is de sortering wel goed zo?
Als de sortering wel goed is kun je kolom A gewoon vullen met de getallen 1 t/m 21.
Je kunt dat eventueel automatiseren door kolom A te vullen met de formule

=RIJ()-4

Maar hoe wordt kolom A nu gevuld?

 

[Miertjee]

Met de functie Rang.Gelijk. Sorry, ik heb alleen het tabelletje uit een ander bestand gehaald en de waardes geplakt, niet de formules, daar had ik even niet over nagedacht. Er zitten nogal wat verwijzingen in het bestand, vandaar.
De functie Rang.Gelijk wil ik graag op de een of andere manier met voorwaarden. Of een andere functie die mijn doel bereikt.

 

[Miertjee]

Hier met formule

 

[popipipo]

Als kolom M de waarden gelijk zijn moet er denk ik gekeken worden naar kolom L voor de juiste volgorde
Maar als kolom L de waarden ook nog gelijk zijn (zoals in voorbeeld rij 14 en 15) waar moet er dan op gesorteerd worden ?

 

[Miertjee]

Klopt, als in M de waarde gelijk is moet er naar L gekeken worden. Soms zijn de waardes in L ook gelijk, zoals in rij 14 en 15 maar lang niet altijd. Rij 16 en 17 zijn ook weer ongelijk, net als rijen 10, 11 en 12. Soms is het gelijk, maar meestal niet. Hoe krijg je dan de rang in kolom A direct correct, zonder dat je handmatig moet corrigeren. Dat kan uiteraard wel, maar ik heb 20 van deze bestanden en meestal niet de tijd om er rustig naar te kijken en het te corrigeren. Als het moet dan moet het, maar als het automatisch kan zou dat zeker schelen. Plus, na 20 bestanden zwemmen de cijfers mij om mijn hoofd, daar heeft excel dan weer geen last van, dus de foutmarge zal kleiner of non-existent zijn.

 

[AHulpje]

Moeten de rangnummers in kolom A altijd uniek zijn?
Zo ja, dan sorteren op de manier zoals omschreven in #2 en kolom A vullen met de formule =Rij()-4 of de getallen 1 t/m 21.
E.e.a. is eventueel met een macro te realiseren die alle 20 bestanden onder handen neemt. Hebben alle bestanden dezelfde indeling?

 

[popipipo]

Bekijk deze opzet eens.

 

[emields]

of deze met vba dus eerst opslaan als xlms en dan het nieuwe bestand openen.

 

[HSV]

Met VBa.

Private Sub CommandButton1_Click()
 With ListObjects("Tabel1")
   .Range.Sort .ListColumns("Punten7"), 2, .ListColumns("Totaalscore6"), , 2, , , 1
End With
End Sub

 

[Miertjee]

@popipipo: dan komt er een tabel bij, dat wil ik nou juist niet. Het originele bestand bestaat uit 13 tabbladen, met op de eerste 2 bladen invullen, 1 met losse gegevens en 10 andere halen daar de gegevens uit. Zo ook het blad waar dit tabelletje op staat. Hij moet juist de overgenomen gegevens in een keer goed in 1 tabel zetten.

@emields: bij mij doet het niks, als ik op de knop sorteren klik, geeft het bestand een foutmelding. Hij staat als .xlsm.

@HSV: Eh....ik heb echt geen kaas gegeten van VBA. Ik had me voorgenomen om me er in te gaan verdiepen, maar ik verzuip als een malle en ik gebruik het te weinig om er een leereffect bij te hebben.

Maar als ik de reacties zo lees, is er dus geen eenvoudige oplossing (of in ieder geval een niet te ingewikkelde) om die ranking in een keer goed te zetten.

 

[Miertjee]

@AHulpje de waardes hoeven zeker niet allemaal uniek te zijn. Als zowel kolom M als L dezelfde waarde bevatten, dan mag de ranking wel hetzelfde zijn.

 

[AHulpje]

Kloppen de punten in de blauwe kolom?
Het lijkt erop dat je de punten van Kind 20 in kolom F optelt bij de punten van Kind 4 in kolom K, 37+50=87, is dat juist?
Kun je in je voorbeeld eens aangeven wat het uiteindelijke resultaat zou moeten zijn?
Hoe wordt nu e.e.a. gesorteerd?

 

[Miertjee]

Even een nieuwe in de bijlage, in kolom A het gewenste resultaat (hopelijk zonder fouten). In kolom B wat excel nu geeft. Ranking gebaseerd op kolom N, waarbij een hogere score in kolom M een kind wel hoger in de ranking moet zetten.

Hij telde inderdaad de verkeerde kinderen bij elkaar op, maar het probleem komt ook zeker voor als dat wel goed gaat. Meestal gaat er een macro voor de sorteringen over de verschillende bladen en uiteindelijk ook deze om alles weer goed te zetten, dus als je er dan uit gaat knippen, gaat het wel eens mis (bij mij in ieder geval).

 

[HSV]

In Kolom O: =M5+N5
In kolom A: =RANG(O5;O$5:O$25)

 

[Miertjee]

Zo wow, ik lees dit, denk waarom zou ik dit doen, moet er even over nadenken en damn, dat is echt geniaal! Wat onnozel dat ik daar niet op gekomen ben. Maar dat lost het probleem wel op.

 

[AHulpje]

Mijn methode en die van HSV naast elkaar, resultaat hetzelfde naar die van HSV is veel eleganter.

 

[Miertjee]

@AHulpje Ah, dat doet het ook, maar volgens mij mis je dan nog de -1 waarde, klopt dat?. Als ik in M11 de score iets aanpas, waardoor regel 11 hoger zou moeten eindigen dan regel 10, gaat de ranking van HSV wel mee, maar die van jou niet, die gaat dan juist verkeerd om. Maar met de ALS formule zou je er op de een of andere manier ook kunnen komen, dat is ook wel goed om te weten.