Reeks met sprongen

[MarnixE]

Goedenavond, ik kan nergens vinden hoe ik een formule kan doortrekken met telkens een andere wijziging dan dat het 1 rij naar beneden gaat. Ik heb namelijk een voorspellingen bestand waarbij ik de ranglijst op het ene blad hebt en op een ander blad de voorspellingen met de punten die hierbij horen.
Tussen iedere deelnemer zitten 37 rijen. Ik wil dus graag dat er op het blad met de stand onder elkaar komt te staan: =Wedstrijdvoorspellingen!A2 en dan daaronder =Wedstrijdvoorspellingen!A39 . Het gaat om aardig wat deelnemers dus om alles handmatig in te vullen is nogal een karwei. Kan iemand mij vertellen hoe ik dit kan invullen en vervolgens gewoon naar beneden door kan trekken?
Ik hoor graag van jullie!

 

[VenA]

Probeer het zo eens. Eventueel even vertalen naar NL functies.

=OFFSET(Wedstrijdvoorspellingen!$A$2;((ROW()-1)*37);0)

 

[MarnixE]

Super dank je, dit werkt inderdaad! Nog een ding dan, ik kan alleen de ranglijst met deze formule niet meer laten sorteren. Of ik nou 'ascending' filter of 'descending' dan blijft alles gewoon staan op hun plek. Is het nog mogelijk dat ik wil kan filteren?

 

[Cobbe]

Voor het sorteren kan je dit toepassen:

Sub Sortering()
Columns(4).ClearContents
 Range("A1:A" & Range("A:A").SpecialCells(xlCellTypeLastCell).Row).Copy
  Cells(1, "D").PasteSpecial Paste:=xlValues
   rijen = WorksheetFunction.CountIf(Range("D1:D1000"), ">""")
    Range("D1:D" & rijen).Sort key1:=Cells(1, "D")
End Sub

Het neemt de waarden uit Kolom A en plaatst ze gesorteerd in Kolom D.

 

[MarnixE]

Bedankt Cobbe, voor uw bericht! Ik moest even kijken hoe ik kon coderen in Excel op Mac omdat ik voorheen op Windows werkte. Echter is het mij niet gelukt om met deze code het voor elkaar te krijgen. Is er wellicht nog een andere optie om de 'OFFSET' waarden wel te kunnen sorteren?
Ik hoor het graag!

 

[Timshel]

Je kunt sorteren als je de formule van VenA vervangt door waarden.
Dat is wat de code van Cobbe doet.

 

[MarnixE]

Daar zat ik inderdaad ook aan te denken, dat kan natuurlijk met 'speciaal plakken' alleen moet ik het dan iedere x handmatig doen of door ergens anders =VALUE() neer te zetten. Maar als ik =VALUE() doe op de cel waarin de naam van de deelnemer staat geeft hij #VALUE! aan, welke formule moet ik dan gebruiken om van de naam een waarde te maken?

 

[E v R]

Nog een ding dan, ik kan alleen de ranglijst met deze formule niet meer laten sorteren.

Zou je eventueel mbv formules kunnen doen, plaats anders eens een gelijkend voorbeeldbestand

 

[MarnixE]

Hierbij een voorbeeld bestandBekijk bijlage Voorspelling voorbeeld.xlsx
Het gaat dus om het blad 'Stand' daar maak ik een stand aan de hand van de voorspellingen op het blad 'Wedstrijdvoorspellingen' in dit voorbeeld heb ik het even met 5 deelnemers gedaan. Als ik het op de hele stand voor elkaar kan krijgen om middels =OFFSET() alles automatisch op het 'Stand' blad te krijgen en dan ook kan filteren kan ik dat natuurlijk daarna ook per wedstrijd doen wat ik ook graag wil. Hoop dat het bestand wat uitkomst biedt?

 

[VenA]

In een extra kolom de gesorteerd lijst.

=INDEX($B$2:$B$6;VERGELIJKEN(GROOTSTE($C$2:$C$6;RIJ()-1);$C$2:$C$6;0))

 

[MarnixE]

In een extra kolom de gesorteerd lijst.

=INDEX($B$2:$B$6;VERGELIJKEN(GROOTSTE($C$2:$C$6;RIJ()-1);$C$2:$C$6;0))

Dat is prima en ziet er goed uit, dank je! Dan nog één ding, kan ik dan nog achter de naam (waar het gesorteerd staat) ook automatisch het aantal punten krijgen? Zodat het een stand is met naam en punten?
Jullie helpen mij in ieder geval fantastisch!

 

[VenA]

Is dezelfde formule alleen een ander bereik

=INDEX([COLOR="#FF0000"]$C$2:$C$6[/COLOR];VERGELIJKEN(GROOTSTE($C$2:$C$6;RIJ()-1);$C$2:$C$6;0))

 

[MarnixE]

Is dezelfde formule alleen een ander bereik

=INDEX([COLOR="#FF0000"]$C$2:$C$6[/COLOR];VERGELIJKEN(GROOTSTE($C$2:$C$6;RIJ()-1);$C$2:$C$6;0))

Dat werkt inderdaad! Echter sorteert hij alleen de namen van de eerste 3 en daarna gaat hij namen herhalen.
Ik heb

=INDEX($B$2:$B$51;MATCH(LARGE($C$2:$C$51;ROW()-1);$C$2:$C$51;0))
=INDEX($C$2:$C$51;MATCH(LARGE($C$2:$C$51;ROW()-1);$C$2:$C$51;0))

Alleen dan sorteert hij de namen uit B2:B51 niet goed. Na de eerste twee die kloppen gaat hij andere namen die in de range voorkomen tussen de 2 en 16x herhalen. Enig idee?

 

[Cobbe]

Dat komt doordat de punten uit kolom T niet correct opgehaald worden.
Nu heb je 2 pers. met punten en de rest met 0 punten, dan neemt deze form. steeds de eerst tegengekomen.

 

[MarnixE]

Dat komt doordat de punten uit kolom T niet correct opgehaald worden.
Nu heb je 2 pers. met punten en de rest met 0 punten, dan neemt deze form. steeds de eerst tegengekomen.

Dat klopt inderdaad, in het totaal klassement zijn er al wel wat meer punten, maar per verschillend aantal punten pakt hij inderdaad telkens de eerste naam die hij tegenkomt. Is er een formule die ik hier dan wel voor kan gebruiken of alleen door te coderen?

 

[VenA]

Bekijk elke formule eens rustig en je zal zien dat iedereen een uniek aantal punten krijgt.

Dus ook graag het bijgevoegde bestandje bekijken.

In kolom C in het bestandje in #10 staat

=OFFSET(Wedstrijdvoorspellingen!$T$37;((ROW()-2)*37);0)+ROW()/100000

 

[MarnixE]

Bekijk elke formule eens rustig en je zal zien dat iedereen een uniek aantal punten krijgt.

Dus ook graag het bijgevoegde bestandje bekijken.

In kolom C in het bestandje in #10 staat

=OFFSET(Wedstrijdvoorspellingen!$T$37;((ROW()-2)*37);0)+ROW()/100000

Ik vroeg me inderdaad af wat daar precies het doel van was, zat er naar te kijken maar kwam er niet uit. Snap nu in ieder geval de uitkomst van de formule, super! Dank je wel VenA