Results 1 to 3 of 3

Thread: Need help with team lists

  1. #1

    Need help with team lists

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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,

    Team 1 Team 2 Team 3 Team 4
    Sam George Jane Martin
    Jack Ian Carl Yvette
    Holly John Rick Andrew

    Team 1 Team 2 Team 3 Team 4
    Sam Jack Holly George
    Ian John Jane Carl
    Rick Martin Yvette Andrew

    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!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts