Need help with team lists

Hello All,

I am stuck since a few days on this workbook where I am trying automate the reverse engineering of existing team lists. Without confusing anyone further, here are the steps of what I need to accomplish.

Context -- The workbook contains team lists, it can have any number of teams and participants in each team. My task is to be able to automate the action of dividing the participants of each team equally across the other teams.

As an example,





There can be any number of teams and any number of participants (including uneven participants in each team).
I want my formula to lookup and return the participant names and number of teams, following this it must automatically populate the new list. Is this asking too much? Is it possible?

I tried to use transpose to reverse a major chunk of the list and then shift the last few names manually but it is not the best process.

Sorry I'm new to excel, please help me!

See attached for suggestion.

This solution involves use of a couple of helper columns to extract the data in a single column format, so it is easier to transpose to the final table.

1. In I1 and I2 I entered the number of teams and max number of players in any one team.

2. I created a dynamic named ranged for the Original table, so that you can add teams/players (you will need to update I1 and I2 accordingly). Go to Formulas tab, Define Name, enter name; Original, and formula: =OFFSET(Sheet1!$A$1,1,,Sheet1!$I$2,Sheet1!$I$1)

3. In column K create first helper column. In K2 enter formula: =IF(ROWS($K$2:$K2)>$I$1*$I$2,"",INDEX(Original,MOD(ROW(A1)-1,$I$2)+1,MOD(INT((ROW(A1)-1)/$I$2),$I$1)+1)) copied down as far as you want.

4. Create another named range called Step1 with formula: =OFFSET(Sheet1!$K$2,,,COUNTIF(Sheet1!$K:$K,"?*"))

5. In column L create second helper column. In L2 enter formula: =IFERROR(INDEX(Step1,SMALL(IF(Step1<>0,ROW(Step1)-MIN(ROW(Step1))+1),ROWS($A$1:$A1))),"") This formula needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER. Then copy down as far as you need.

6. Create last named range called Step2 with formula: =OFFSET(Sheet1!$L$2,,,COUNTIF(Sheet1!$L:$L,"?*"))

7. Now final Table layout starting in N2 with formula: =IF(COLUMNS($A1:A1)>$I$1,"",IFERROR(INDEX(Step2,$I$1*(ROWS(A$1:A1)-1)+COLUMNS($A1:A1)),"")) copied across and down as far as you need to cover all possible combinations of columns/rows you'll need.

Hopefully this helps.

Sorry for the delay in responding..!! Thank you so much NBVC, you solved my problem..!