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.

=LET(
  a,FILTER(A3:A100,B3:B100="A"),
  b,FILTER(A3:A100,B3:B100="B"),
  c,DROP(REDUCE({0,0},SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,HSTACK(INDEX(a,y),IF(SUM(--(INDEX(x,,1)=INDEX(a,y))),MAX((INDEX(x,,1)=INDEX(a,y))*INDEX(x,,2))+3,  MIN(MAP(SEQUENCE(y),LAMBDA(z, IF(SUM(--(INDEX(x,,2)=z)),"",z))))))))),1),
d,DROP(REDUCE({0,0},SEQUENCE(ROWS(b)),LAMBDA(x,y,VSTACK(x,HSTACK(INDEX(b,y),IF(SUM(--(INDEX(x,,1)=INDEX(b,y)))+SUM(--(INDEX(TAKE(a,y),,1)=INDEX(b,y))),MAX((INDEX(x,,1)=INDEX(b,y))*INDEX(x,,2), IFERROR(((INDEX(TAKE(c,MIN(y,ROWS(c))),,1)=INDEX(b,y))*INDEX(TAKE(c,MIN(y,ROWS(c))),,2)),100))+3,  MIN(MAP(SEQUENCE(y),LAMBDA(z, IF(SUM(--(INDEX(x,,2)=z)),"",z))))))))),1),
 IFERROR(HSTACK(WRAPROWS(TAKE(SORTBY(c,INDEX(c,,2)),,1),3),WRAPROWS(TAKE(SORTBY(d,INDEX(d,,2)),,1),3)),"")
)

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.