1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Hi Guest, welcome to the TES Community!

    Connect with like-minded education professionals and have your say on the issues that matter to you.

    Don't forget to look at the how to guide.

    Dismiss Notice

Sports day groups using excel

Discussion in 'Computing and ICT' started by JohanneTripp, Jun 7, 2019.

  1. JohanneTripp

    JohanneTripp New commenter

    Hey,

    Does anyone know if it possible to create groups using excel spreadsheets for sports day.

    Say I'm organising two year groups at a time (approx 120 students or less)
    into 10 groups. The students are in three houses.

    I need the groups of 12 or so students sorted as fairly as possible by houses.

    I'm doing it manually but I'm sure there must be a better way!
     
  2. tjra

    tjra Occasional commenter

    It sounds like it, but I'm not 100% sure what you're asking.

    If you had a table of students with their forename, surname, year, house and group in different columns then you could use sorting and filtering to arrange this. This video might help:

     
  3. elder_cat

    elder_cat Established commenter

    Sports Day Spreadsheet

    120 students split between 3 houses.
    10 groups of 12 students.
    Each group should contain 4 students from each house.

    Assuming you're OK using macros to copy, move, and delete, it should be fairly straightforward.

    1. Create sheets in spreadsheet:

    a. One sheet containing All students:

    Columns:

    Name of Student.
    Year (Year 1 or Year 2).
    House (House A, House B, or House C).

    b. One sheet each for Groups 1 - 10:

    Columns:

    Name of Student

    2. On the main sheet:

    Sort the sheet by Year, followed by House.
    Set ranges containing Year 1 and Year 2.

    3. To get students for Group 1:

    Choose two student names at random from House A range, in Year 1.
    Add these students to Group 1.
    Remove these students from the main sheet.

    Choose two student names at random from House B range, in Year 1.
    Add these students to Group 1.
    Remove these students from the main sheet.

    Choose two student names at random from House C range, in Year 1.
    Add these students to Group 1.
    Remove these students from the main sheet.

    Choose two student names at random from House A range, in Year 2.
    Add these students to Group 1.
    Remove these students from the main sheet.

    Choose two student names at random from House B range, in Year 2.
    Add these students to Group 1.
    Remove these students from the main sheet.

    Choose two student names at random from House C range, in Year 2.
    Add these students to Group 1.
    Remove these students from the main sheet.

    5. Repeat for Groups 2 to 10.

    Yours should end up with no students names left on the main sheet, and names of 12 students on each of the sheets for Groups 1 - 10.
     
  4. GeordieKC

    GeordieKC Occasional commenter

    Try something along the lines of:

    Step 1: Add an additional column and fill cell in that column with a random number: using =RAND()
    Step 2: Copy that column and then paste special Values (this gives you a list of random values that does not keep changing)
    Step 3: Sort the list first by House and then by the Random column
    Step 4: top 4 names in each house go to the first group, next four to the second group and so on,
     
  5. border_walker

    border_walker Established commenter

    I would / have done something similar in the past. Equally you could manipulate the random numbers so that they are the group numbers. There are many simple ways of doing this.
     

Share This Page