Ranking and Grouping Data

Hello, I'm working on a project where I need to group golfer scores into groups of 6 with certain criteria. I thought about using Solver, but I have over 500 golfer scores in my database. I've attached a spreadsheet for review.

Goal: Maximize the score of each group
Criteria:
1) Each group must have 6 scores in it: 3 from members from Team A and 3 from members from Team B
2) No golfer name may be repeated in the same group (for example, there's 3 Corey Conners listed here, but he may only be in a single group once)
2B) Some golfers posted scores for either team, so again as is the example with Corey Conners, he has a score entry for Team A and Team B

I'm looking to do this via formula, and not VB if possible.

Thank you for any assistance.

If I may elaborate, I think it's easy to rank all the scores from Team A and group them accordingly (Group 1 has Team A ranks 1, 2, 3... Group 2 has Team A ranks 4, 5, 6, etc.), however, the wild card is a name can't be listed twice. So in the example above, as "Corey Conners" has 3 entries, he can only show up in Group 1 once, then potentially Group 2 once, etc.

Hi Dave, thanks for the reply. I think either a spill or separate formula works, but ultimately the groups are fused together (3 members from Team A and 3 from Team B). "Corey Conners" will have entries on both Team A and Team B, but he can't be listed under Team A and Team B in the same group.

So, say for instance Corey Conners has the top 5 scores overall, 3 of them while playing for Team A and 2 while playing for Team B. I'd expect him to show up in Groups 1, 2, 3 for his Team A, while in group 4 and 5 for Team B. So, he's included in the "Top 5" groups, but only once in each, if that makes sense.

Done. See attached. I also added a column "D' to show the placement of each player's score within the groups. Hope this makes sense. It definitely has some logic built into it to properly allocate scores. Also, I sorted the scores in Column "C" to show the order of importance. Again, I'm trying to essentially get to Group 1 being the 6 best, name-non-duplicated scores of the year.

It is a bit complicated, yes. I think UNIQUE is a useful formula here, to ensure 6 names are not duplicated across one Group. The syntax of having a formula loop back to grab prior results in a non-linear fashion might be more of a programming approach, unfortunately.

Yes, that's fine. Thank you kindly for chaperoning this through.

I'm not sure whether this formula is correct?

You must check it by yourself.

Please Login or Register  to view this content.

This is an impressive formula. It will take me a bit to learn and digest. I'm going to deploy it against my local copy of the full data set and see if it works.