Populating teams from a dynamic player list

[FivestarMac]

This is a golf league of 9 teams of up to 20 players each from a player list currently at 135 but could rise to 9 x 20 as new players arrive and sign up. As a new player enters the Players List he is assigned to a team identified by the initials of its captain. Using that info I wish to have the teams populated under the captains on a second sheet.

[FlameRetired]

Most of this does not seem to be a problem. However in order to initiate the formulas we will need to know how to determine when each team begins in the list ... ie why is Coppola, Jay team (JC) first, Wait, Jim (JW) second etc, etc?

Also what in the list (besides colors ... Excel formulas cannot read colors) tells us who the captains are?

[FivestarMac]

The 20-man (max) teams can be in any order. Nothing defines Captains other than the red color. The Captains on the Team sheet can be simply entered as text. Once a man is assigned to a team he never changes to another team. I just want to be able to insert new players alphabetically into the Player sheet, then, using the initials of the Captain (JC) populate the existing teams as new players sign up for the league.

[FivestarMac]

Also, I would like the Players list to populate the Teams sheet...for the existing 135 players as well as any new people who may join the league.
Jim

[Pete_UK]

You don't seem to be using column A of the Payers sheet - can I use this for a helper formula?

Pete

[FivestarMac]

Sure.
Jim

[Pete_UK]

You seem to want your captains listed at the top of each team, although there is no way to identify them as such (as Dave mentioned earlier, a formula can't pick out a colour), so would it be possible to have another column in the Players sheet (say a new column D) that contains a Y to indicate the captains?

Pete

[FivestarMac]

Yes. I guess I thought the captain's initials would serve as the identifier as to who is on a given team...but then I have been unable to do this myself, so I will wait to see what you have in mind.
Jim

[FlameRetired]

Yes. I guess I thought the captain's initials would serve as the identifier as to who is on a given team...but then I have been unable to do this myself, so I will wait to see what you have in mind.
Jim

Yes I understand that, but without a way to initiate each position 1 for each team you are left to manually type each captain into those positions. It defeats the whole purpose behind automating what you wish to do.

Maybe like Pete suggests we can put something in column A for a helper ... "Captain" ... ?

[FivestarMac]

Yes, there are no constraints on what you can add to this workbook,,,it's a work in progress, yet to be put into action.

[Pete_UK]

Okay, I've got the names sorted out now, but there is a football match on, so I'll pick this up later.

Pete

[FivestarMac]

OK, thanks.

[FlameRetired]

Yes, there are no constraints on what you can add to this workbook,,,it's a work in progress, yet to be put into action.

Good deal.
It the attached 'Players' Column1 has "Captain" where applicable.

The 'Teams' sheet is a bit of a Medusa's head ...

To start with in column B this identifies the captains and their respective team mates.

Please Login or Register  to view this content.

Then in column C find the captain's initials.

Please Login or Register  to view this content.

To identify the scores by player and dates in column D

Please Login or Register  to view this content.

Copy that range and paste into F2:AF2. Then to get the Best 5 in column E

Please Login or Register  to view this content.

[FivestarMac]

Way beyond anything I could have done, thanks so much.
Jim

[FivestarMac]

In my initial review I see that Jim Wait is On the Teams sheet in cell B162 and also cell B176. Can you identify what happened there, please.
Jim

[Pete_UK]

Hello again.

I've taken a different approach than Dave, and tried to automate as much as possible. On the Players sheet I've inserted two new columns, D and E, and also used column A. Column D allows you to indicate the captains by putting a Y against their name, so that your data input here (apart from the scores) will be the names in column B, the team initials in column C and a Y against the captains in column D - I've coloured the headings yellow for clarity.

I've used column E as a simple helper column to identify the captains and give each a unique sequential number, by means of this formula in E2:

=IF(D2="Y",MAX(E$1:E1)+1,"-")

this is copied down to the bottom of the table. In column A I have used this formula in A2 (also copied down):

=IF(C2="","",IF(D2="Y",C2&"_0",C2&"_"&COUNTIF(C$2:C2,C2)-COUNTIFS(C$2:C2,C2,D$2:D2,"Y")))

Each player is given a unique code made up of the team initials, an underscore, and a unique number. Captains are numbered zero, to ensure that they come at the top of the list on the other sheet, whereas other players are numbered in sequence so that they will appear in alphabetical order on the team sheets (assuming your table is maintained in order, as you say). One consequence of this is that the team captains will be listed in alphabetical order as well, so the ordering on the other sheet will be different to the way you had set it up. I've also set up conditional formatting on the names column, so that captains will be shown with a red background and yellow bolded foreground, so there is no need to manually apply the colours.

On the Teams sheet you can generate the numbers 1 to 20 repeatedly by putting this formula in cell A2:

=MOD(ROWS($1:1)-1,20)+1

and copying down. Similarly, the team initials in columns C can also be generated automatically using this formula in C2:

=IF(A2=1,INDEX(Players!$C:$C,MATCH(COUNTIF(A$2:A2,A2),Players!$E:$E,0)),C1)

The names can be generated in column B using this formula in B2:

=IFERROR(INDEX(Players!$B:$B,MATCH(C2&"_"&A2-1,Players!$A:$A,0)),"")

and copying this all the way down. Again, I have applied conditional formatting down this column to turn the captains' names red, so you don't need to do that manually. Notice, as I mentioned above, that the teams are in a different sequence than how you started. The scores for 6th November can be obtained with this formula in D2:

=IFERROR(INDEX(Players!$G$2:$AH$136,MATCH($B2,Players!$B$2:$B$136,0),MATCH(D$1,Players!$G$1:$AH$1,0)),0)

This should be copied all the way down, and it returns zero against blank names to avoid the #N/A errors that you had. This formula matches the header against the headers in the other sheet, so you can copy it into columns F, G, H etc. to pick up the scores for other weeks. I've done this for those columns in the attached file.

Finally, the top-5 sum for each team can be obtained using this formula in E2:

=IF(A2=1,SUMPRODUCT(LARGE($D2:$D21,{1,2,3,4,5})),"")

Copy down to the bottom, but it will only show the result for each team on the first row, against the captain's name.

If you insert a new name in the first sheet (along with initials), then it should be picked up automatically as long as the formulae in that table are also maintained. If you add a new captain to start another team, then the formula in the Teams sheet should be copied down another 20 rows.

Hope this helps.

Pete

[FlameRetired]

In my initial review I see that Jim Wait is On the Teams sheet in cell B162 and also cell B176. Can you identify what happened there, please.
Jim

2 mistakes in my formula column B. My bad . Try instead

Please Login or Register  to view this content.

[FivestarMac]

In these chaotic days around the world it is comforting to relate with those, like yourself, who trade in careful, weighed, analytical logic. Too bad those in leadership positions do not employ your considered approach to their problems as you have done to mine. This forum demonstrates to me, better than any other facet of my life, how well informed people, assisting others, can quickly solve problems and move forward. In my mind it's a bigger thing than Excel. Thanks for your role in it.
Jim

[FivestarMac]

(I wish to make sure I thanked both of you.)

In these chaotic days around the world it is comforting to relate with those, like yourself, who trade in careful, weighed, analytical logic. Too bad those in leadership positions do not employ your considered approach to their problems as you have done to mine. This forum demonstrates to me, better than any other facet of my life, how well informed people, assisting others, can quickly solve problems and move forward. In my mind it's a bigger thing than Excel. Thanks for your role in it.
Jim

[FlameRetired]

You are welcome. Glad to help. Thank for the feedback, your kind words and marking your thread Solved.

[Pete_UK]

Hi Jim,

I've had difficulty getting my PC started today (and for the last few days), and in the end I had to un-install a Windows update from Tuesday night/Wed morning, so I've only just seen your posts.

Glad to help, and thanks for those considered words.

Thanks also for the added rep (and to Dave as well).

Pete

EDIT: I've just noticed that you and Dave joined the forum almost at the same time.