How to Randomly Assign Participants to Equal Sized Groups in Excel

How to Randomly Assign Participants to Equal Sized Groups in Excel | Group Generator - No Repeats

Рет қаралды 47,195

Күн бұрын

Download the featured file here: www.bluepecantraining.com/wp-...
In this video I demonstrate how to randomly assign participants to equal sized groups using Microsoft Excel. This video uses some functions that are only available in Excel 365.
Table of Contents:
00:00 - Introduction
00:37 - STEP 1: House the data in an Excel table
01:16 - STEP 2: Create a random number for each participant using RAND
02:01 - STEP3: Rank the random numbers using RANK.EQ
02:23 - STEP 4: Divide the RANK by the group size
02:52 - STEP 5: Round the numbers up using CEILING.MATH
03:13 - STEP 6: Combine formulas into one formula
03:55 - STEP 7: Use the SEQUENCE function to generate group headings
04:45 - STEP 8: Use the FILTER function to list names under the correct group headings
07:43 - STEP 9: Apply conditional formatting
------------------------

Пікірлер: 26

@muthusubramanii9286 Жыл бұрын

After so many searches got this video and its amazing.

@IvanCortinas_ES Жыл бұрын

A very interesting working procedure Chester. Excellent video. Thank you.

@user-vu5xu2es5y 5 ай бұрын

Thank you so much for the tutorial! It is very helpful

@user-ve3br9zv1p 5 ай бұрын

Very helpful!!

@mymatemartin Жыл бұрын

That was great. Thanks

@patrickschardt7724 Жыл бұрын

Interesting concept and wonderfully executed. I wonder if this could be done in a single cell or a formula with the new dynamic of Ray’s

@darcycook5852 11 ай бұрын

This is great but is there a way of doing this with unequal sized groups (the groups would be assigned as a percentage of the total number of participants - ie 30% in one group, 20% in another, etc)?

@strongk1n1 2 ай бұрын

Thank you for the video. is it possible to assign girls and guys to specific cells? I want to have a grouplist like 1- guy 2- girl 3- guy 4-girl 5-guy/girl

@amsr27 Жыл бұрын

This is helpful. Chester, could you also help in case after the first grouping done, there is a need to have more rounds of similar groups, where pairing is to be done in a way that there are no duplicate pairings in any subsequent rounds. For eg, in your example, in first round of grouping, Jeri and Teofila have been paired. So in next round, these 2 should not be paired together and we get a new list of pairings with other names.

@dluber1 Жыл бұрын

Thanks, Chester, very helpful. Amit - Yes, it's possible, just make two such tables as the author describes then do a joint lookup. I had a similar problem assigning groups of applicants to readers that you can adapt. In my case, it was scholarship applications. I had 50 applicants and 40 readers, and assigned each reader 5 applicants, so that each application is read by 4 readers, and no reader gets duplicate applicants. So I made two tables, each with the same numbers of groups, and then matched readers to applicants by group number. The Applicant table group size is based on division by 5 applications per reader, which gives 10 groups for 50 applicants. The Reader table group size is by division by 4 readers per applicant, giving 10 groups for 40 readers. Then I made a separate column of all the readers and their group numbers and used the Filter function to list all the applicants with matching group numbers, similarly to shown here but added the Transpose function to display them horizontally. If the numbers of applicants and readers are not evenly divisible, there will be leftovers, so just manually reassign a few to even it out. In your case, the total number of groups would be groups times rounds, so you'd need to individually identify them. E.g., Round 1-Group 1, 2, n to Round X-Group 1, 2,..n for X rounds of n groups. So if you have say 4 rounds of 10 groups, it’s the same problem I had. I can’t share a spreadsheet with PII but can give you redacted screenshots. No way to post them on a YT comment apparently…

@user-hl4dl3rs6o 9 ай бұрын

This is exactly what I need to do as well.

@mwils1111 3 ай бұрын

This is amazing thank you. Is there a way in which you can add a condition - so that the random groups are balanced according to, for instance, the skill rating 1-3 of each person?

@user-hl4dl3rs6o 9 ай бұрын

Hi! I watched your video and it's great! I have an additional question though. Is it possible to make 4 different groups at the same time with the same people so that each person is in a group with different people each time?

@FhnLadybug Ай бұрын

This is very similar to what I want to do, but not quite. What I’m looking to do is assign people to tables/groups with a certain number in each group. So, let’s say we have 10 tables with 8at each table. And I want some at every table, even if it’s not 8. If one table has 6 and another has 8, that’s okay. So if I only have 40 people, I still want some at each table. I know I can change the group size, but is there another way to do this? Also, can I add participants later?

@lewoo2 Ай бұрын

Do you know how to then apply formula so that students won’t be in another group with the same people?

@greensal86 8 ай бұрын

Great tutorial. I was wondering if you can help with this request. I have a list of items (merchandise) along with their MSRP. I would like to group random items in groups such that the MSRP price of each group equals a value defined by myself. Is that possible?

@fabianmoll2690 3 ай бұрын

Dear Mr. Tugwell, is there an option to do a covariate adative randomization in excel as well? Thank you so much for your explanation. It is very helpful.

@coyninho Жыл бұрын

Can this be done with a fixed number of groups, rather than group size?

@bartolomy01 11 ай бұрын

This actually helped me on what I'm trying to do but I have repeat names. For example, I have I can have (2) Xuan Wilkey, (5) Winston Kell, and (10) Jeri Huls. I would like to pair them by groups of 2 along with the rest of the list but it would give me in a group for example two Winston Kell. It has to be a unique combination, no Winston-Winston or Winston-Xuan, Xuan-Winston. Any help would be much appreciated 😀

@user-uh7kc7km3f 5 ай бұрын

This is great and helpful. But I would like to know how to take this a step further... I have 25 kids that need to be randomly assigned into 6 groups. (this video showed how to do this) But these kids will be together for 6 days and there will be a new group each day, with no two of the same kids ending up in the same group on the following five days. Help?

@jennybergman8622 Жыл бұрын

Hi! This is great! How can i do it with more criteria? Lets say I want them to work in groups with team members they have not worked with before. An/or i want it to consider gender as well :)

@hannahrosenussbaum17 10 ай бұрын

love this. Is there a way to then repeat it randomly?

@brunomuyllaert 2 ай бұрын

What I did was just place a new =RAND() formula in column C (whereas he copy-pasted it to have the values set). Every time I set a group size (even if I input a same numer), I'll get random groups.

@mossyonyx Жыл бұрын

Somehow I have managed to get this wrong and rand gave me date and time lol

@janicesalchert5479 Жыл бұрын

What if you want to create groups of 4 across a group of names that changes each month. So across 6 months, I want 3 groups of 3 or 4 people (the group has 11 people). I want a different group of names each month until after 6 months, every person has been in a group with each of the other 10 members of the team.

@dluber1 Жыл бұрын

Janice - see my reply to Amit above, similar solution - two tables.