Hope somebody can help with this item.

For the gymnastic club here I made several result lists. During the years they improved in automation. But I kept on thinking about a more better method with less handwork.

I’m almost there I think. For this Forum I made an extract from the datasheet (the data comes from other sheets with some functions, but for now I hardcopied them in).

On this sheet you’ll see al kinds of data which child it’s about, what club, etc. but most important for my question is column E where the Categorie is given. As you can see children are classified in different categories. Each categorie can have one to many participants, spread over more groups (column B). But they compete against each other.

So in column AB I want to rank them for their categorie.

Of course I can do that ‘by hand’ (rank the rating either from column Z or AA and give for the first categorie by hand in that’s the group from row 5 until 29 (with 28). But that’s typical something I would like Excel to do for me: find the groups and rating them automatically.

I tried a lot with index, match, offset and such, but can’t get it.

You find already some testing numbers in the sheet, based on how the calculations go so don’t bother about them and  feel free only to give an example for one categorie.

Any help would be gladly appreciated!

EDIT: for this year, for this round this is the setting for the categories. But I have to go two other rounds this year with a completely different setting (les categories, more children in the different categories. So that's why I want that part 'automated'. Hope this is clear. It's not about this sheet itself, it's part of a bigger 'problem'. Thanks!