index en vergelijken

[Hans04]

Hallo allen,

Ik ben op zoek naar een oplossing voor het volgende:
uit de kolom met "punten" zoek ik de hoogste waarde, met het bijbehorende "gem" én de bijbehorende "letter". Deze waarden komen in de kolommen die rood omrand zijn.
Ik denk dat ik daarvoor index en vergelijken nodig heb, maar zie de oplossing nog niet.

Het groene deel geeft de werkelijke stand aan.
Het komt soms voor dat enkele waarden exact gelijk zijn, dan is de volgorde niet belangrijk, maar zou dan alfabetisch kunnen zijn.

De sheet maakt het een en ander denk ik duidelijk.
Kan zijn dat een mogelijke oplossing als eens gepasseerd is. Heb ik dan gemist.

Hoop dat iemand hiervoor een oplossing heeft.

Hans

 

[MarcelBeug]

Bekijk bijlage plaatsing MB.xlsx
Zie bijlage. Oplossing met matrixformules, bijvoorbeeld in J2:

=INDEX($B$2:$B$13;KLEINSTE(ALS($A$2:$A$13=I2;RIJ($A$2:$A$13)-RIJ($A$2)+1);AANTAL.ALS(I$2:I2;I2)))

 

[MarcelBeug]

Kleine verbetering: als je de formule in J2 aanpast als volgt (bevestigen met Ctrl+Shift+Enter), dan kun je hem doorvoeren naar rechts en naar beneden:

=INDEX(B$2:B$13;KLEINSTE(ALS($A$2:$A$13=$I2;RIJ($A$2:$A$13)-RIJ($A$2)+1);AANTAL.ALS($I$2:$I2;$I2)))

 

[Hans04]

Marcel,

De bedoeling is dat de formule in de rode kolom wordt uitgevoerd. De groene is als voorbeeld wat de uitkomst zou moeten zijn.
Ik kan de formule wel aanpassen naar op het juiste deel (kolom F), maar de formule geeft niet de juiste waarde in kolom "gem2" die bij het betreffende getal van de waarde in "hoogste" hoort.

Hans

Kleine verbetering: als je de formule in J2 aanpast als volgt (bevestigen met Ctrl+Shift+Enter), dan kun je hem doorvoeren naar rechts en naar beneden:

=INDEX(B$2:B$13;KLEINSTE(ALS($A$2:$A$13=$I2;RIJ($A$2:$A$13)-RIJ($A$2)+1);AANTAL.ALS($I$2:$I2;$I2)))

 

[MarcelBeug]

Bekijk bijlage plaatsing MB2.xlsx
Hierbij de aangepaste versie.

 

[Hans04]

Bekijk bijlage 248039
Hierbij de aangepaste versie.

Marcel,

De uitkomst is niet juist. Zie bijgevoegd voorbeeld.
De pijlen geven aan welke regels niet juist zijn. De volgorde in de groene tabel is handmatig ingevoerd ter verduidelijking van wat de juiste volgorde zou moeten zijn.

Hans

 

[MarcelBeug]

... Het komt soms voor dat enkele waarden exact gelijk zijn, dan is de volgorde niet belangrijk, maar zou dan alfabetisch kunnen zijn....

 

[MarcelBeug]

Of was je misschien vergeten om aan te geven dat er eerst gesorteerd moet worden op de hoogste waarde, dan op gemiddelde en pas dan eventueel nog alfabetisch?

 

[MarcelBeug]

Bekijk bijlage plaatsing MB3.xlsx
Dan zou dit het gewenste resultaat moeten opleveren.
Ik heb ook gelijk de formule in kolom E maar even aangepast.

De formules op regel 2, en naar beneden gekopieerd:

E2: =GROOTSTE($A$2:$A$13;RIJEN(E$2:E2))
F2: =GROOTSTE(ALS($A$2:$A$13=$E2;$B$2:$B$13);AANTAL.ALS($E$2:$E2;$E2))
G2: =INDEX(C$2:C$13;KLEINSTE(ALS($A$2:$A$13=$E2;ALS($B$2:$B$13=$F2;RIJ($A$2:$A$13)-RIJ($A$2)+1));AANTALLEN.ALS($E$2:$E2;$E2;$F$2:$F2;$F2)))

F2 en G2 zijn matrixformules, bevestigd met Ctrl+Shift+Enter.

 

[HSV]

Andere benadering (de laatste twee zijn matrixformules).
E2:

=GROOTSTE($A$2:$A$13;RIJ()-1)

F2:

=GROOTSTE(ALS($A$2:$A$13=E2;$B$2:$B$13);AANTAL.ALS($E$2:E2;E2))

G2:

=INDEX($C$2:$C$13;VERGELIJKEN(E2&F2;$A$2:$A$13&$B$2:$B$13;0))

 

[MarcelBeug]

@Harry:
Jouw E2 is op zich wel goed, zolang er geen regels boven regel 2 worden ingevoegd (of verwijderd). Mijn E2 gaat dan nog steeds goed.
Jouw G2 levert dubbele letters op bij ex aequo's van hoogste en gemiddelde.

 

[VenA]

Het is een beetje onduidelijk hoe je aan de informatie komt en wat je er verder mee doet.

Zou het ook met een beetje VBA mogen. De wijzigingen komen ergens vandaan en dan kan een Change event het ook oplossen.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [a1].CurrentRegion) Is Nothing Then
    Application.ScreenUpdating = False
    ar = [a1].CurrentRegion
    [M1].Resize(UBound(ar), UBound(ar, 2)) = ar
    With [M1].CurrentRegion
        .Sort [M1], xlDescending, [N2], , xlDescending, [O2], , xlYes
    End With
End If
End Sub

 

[HSV]

@Harry:
Jouw E2 is op zich wel goed, zolang er geen regels boven regel 2 worden ingevoegd (of verwijderd). Mijn E2 gaat dan nog steeds goed.
Jouw G2 levert dubbele letters op bij ex aequo's van hoogste en gemiddelde.

1. daar was ik ook niet vanuit gegaan.
2. daar heb je een punt.

=INDEX(C$2:C$13;KLEINSTE(ALS($A$2:$A$13=$E2;ALS($B$2:$B$13=$F2;RIJ($A$1:$A$12)));AANTALLEN.ALS($E$2:$E2;$E2;$F$2:$F2;$F2)))

 

[Hans04]

Marcel, Harry en VenA,

Allen bedankt voor het meedenken en puzzelen.
Met de oplossing van Marcel en Harry kan ik voorlopig weer vooruit.
Ben met een toernooi bezig en heb er al profijt van gehad.

Naar de oplossing van VenA kijk ik later nog eens.

Hans