random team assignment excel

Return to Excel Formulas List

How to Make Random Groups in Excel & Google Sheets

Editorial Team

Reviewed by

Steve Rynearson

Download the example workbook

This tutorial will demonstrate how to make random groups in Excel and Google Sheets.

Random Groups

To randomly assign items (data, people, etc.) to groups we can use the RANDBETWEEN and CHOOSE Functions:

RANDBETWEEN Function

The RANDBETWEEN Function generates random numbers between two integers. In this example it will generate numbers 1, 2, 3, or 4.

CHOOSE Function

The CHOOSE Function selects an item from a list by it’s position.

By combining the two functions we can randomly “choose” an item from a list, thus assigning people to groups as in the first example.

Random Groups – Same Size

The example above will assign people to completely random groups. The resulting groups may not be of the same size.

To assign people to same sized groups, we can use the ROUND, ROUNDUP, and RANK Functions:

This calculation is a bit more complicated, so let’s walk you through it.

RAND Function

First, we use the RAND Function to generate random decimal values between 0 and 1 for each person:

Now that we have random numbers for each person we have a way to split them into groups.

RANK Function

Next we will use the RANK Function to identify where each random value “ranks” in the list of random values.

The RANK Function will return a number between 1 and the total number of people in the group. The largest random number will receive 1, second largest will receive 2, etc.

Next we will divide the result of the RANK Function by the desired group size. (ex. 4 for a group size of 4 people per group).

Notice that this returns decimal values between 0 and 4.

ROUNDUP Function

Last, we will apply the ROUNDUP Function to the result to round the decimal values up to integer values 1,2,3, and 4.

This gives us four groups of equal size.

Note: Group 4 only has 1 person assigned, because there are not enough names shown to fully populate group 4.

Note 2: By setting the num_digits input to 0, we tell the ROUNDUP Function to round up to the nearest integer.

Random Groups in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.

AI Formula Generator

Try for Free

• Excel Help Bot
• Formula Generator
• Formula Explainer
• VBA Code Generator
• VBA Code Commenter
• Excel Template Generator

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

• Automatically Graded Exercises
• Learn Excel, Inside Excel!

Free Download

Randomly assign data to groups

Related functions .

To randomly assign rows of data to arbitrary groups, you can use the RANDBETWEEN function with the CHOOSE function . In the example shown, the formula in F5 is:

As the formula is copied down the column, it will return a random group ("A", "B", or "C") at each new row.

Note: this approach will create groups of different sizes. If you need to assign random groups with a fixed size, see  this formula .

Generic formula

Explanation .

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below.

The CHOOSE function

The CHOOSE function returns a value from a list of values using an index number. The index number is provided as the first argument, and the values to be selected follow. For example, if we have a list of three colors ("red", "blue", and "green"), we can configure CHOOSE to return each color in turn with the following formulas:

Notice that CHOOSE uses the index number to select the "nth" value from the list of values. The values can be customized in any way you like and the only requirement is that the index number be valid for the number of values provided. Of course, in this example, we don't want to hardcode an index number into CHOOSE, we want a random index number. For this, we can use the RANDBETWEEN function.

The RANDBETWEEN function

The RANDBETWEEN function generates a random number between two integers, provided as the bottom and the top . For example, to generate a random number between 1 and 10, you can use RANDBETWEEN like this:

When Excel's calculation engine updates a worksheet, RANDBETWEEN will generate a random number between 1 and 10.

CHOOSE with RANDBETWEEN

The behavior of RANDBETWEEN will work perfectly for this problem. We have three possible groups ("A","B","C") so we need a random number between 1 and 3, which we can get like this:

The final step is to embed RANDBETWEEN into the CHOOSE function as the index number like this:

This is the formula that appears in cell F5 in the example shown. When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3. This number is delivered directly to the CHOOSE function as the index number, and CHOOSE returns the corresponding color as a final result. You can use this approach whenever you need to assign random text values to each row in a data set. Just be sure to adjust the second argument in RANDBETWEEN, top , to match the number of values provided.

Stopping automatic recalculation

Be aware that RANDBETWEEN is a volatile function and will recalculate whenever there is any change to a workbook, or even when a workbook is opened. To force a recalculation, you can press the F9 key. Once you have a set of random assignments, you may want to stop the formula from returning new results. The classic way to do this is to use Paste Special:

• Select all cells that contain the CHOOSE and RANDBETWEEN formula.
• Copy to the clipboard with Control + C.
• Open the Paste Special window with the shortcut Control + Alt + V.
• Select "Values" and click OK:

After you press OK, all formulas will be replaced with static values.

Dynamic array formula

In the current version of Excel (Excel 2021 or later) you can use a single dynamic array formula to generate all random values at once. One option is to use the RANDARRAY function with CHOOSE like this:

The core idea of this formula is the same as the original formula above. However, instead of RANDBETWEEN, we use RANDARRAY, which can generate an array of random numbers in one step. To figure out how many random numbers to generate, we use the ROWS function on a range corresponding to the first column of the data. This saves us the step of telling RANDARRAY how many rows we need. In this case, ROWS returns 100, because there are 100 rows in the range B5:B104. Simplifying, we now have:

Next, RANDARRAY generates an array of 100 random numbers between 1 and 3. The result is returned to CHOOSE as the index_num argument, and CHOOSE uses the random numbers to return an array that contains 100 random groups. This array lands in cell F5 and spills into the range F5:F104.

INDEX alternative

It is also possible to use the INDEX function instead of CHOOSE in a formula like this:

Like CHOOSE, INDEX retrieves a value based on an index number. INDEX however accepts the values all at once in the first argument, called array . In the formula above, the values "A", "B", and "C" are provided as an array constant to INDEX as the array , and RANDBETWEEN is used as before to generate a random number between 1 and 3.  The RANDARRAY version of the formula with INDEX looks like this:

One advantage of INDEX is that the array constant can be replaced with a range on the worksheet. In other words, you can enter group names into a range and provide that range to INDEX. The CHOOSE function will not accept a range of values; it requires that values be provided separately.

Note: the formulas on this page will create completely random groups. One result is that the total number of rows assigned to each group will vary. If you need to assign random groups with a fixed size (i.e. randomly assign people to teams of 6), see the example on  this page .

Related formulas

• Randomly assign people to groups

• Random number between two numbers

• Random date between two dates

• Random text values

• Random value from list or table

• Random number from fixed set of options

Related functions

• RANDBETWEEN Function

The Excel RANDBETWEEN function returns a random integer between two given numbers. RANDBETWEEN recalculates each time a worksheet is opened or changed.

• CHOOSE Function

The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.

• RANDARRAY Function

The Excel RANDARRAY function generates an array of random numbers between two values. The size or the array is specified by rows and columns arguments. The generated values can be either decimals or whole numbers.

• ROWS Function

The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.

Related videos

• How to pick names out of a hat with Excel

• How to randomly assign people to teams

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related Information

Get training, quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

Random Group Generator Template [FREE Download]

If you’re a teacher or a trainer, creating groups of students/participants is a common task. For example, you may want to create groups to conduct a quiz or a team building activity.

And in most of the cases, you need these groups to be random.

Today, I am sharing a random group generator template that will make it super easy for you to create a group of students/participants.

All you need is the list of students or participants and specify how many groups you want to create.

Random Group Generator Template

Here is a demo of how this random group generator (or random team generator) template works:

The list of students/participants is in A2:A17. If you have a longer list, simply add the names in it.

Cell E2 has the number of groups that you want to create. Based on the number you enter, you would get the groups and the names in each group in columns G to P. As of now, I have created it this template for a maximum of 10 groups.

Once you have entered the number of groups you want in cell E2, click on the ‘Create Teams’ button to randomly generate the groups of names.

Download the Random Group Generator Template

How this Excel Template Works

There are a couple of cool Excel features and a few helper columns that make this random group generator template in Excel.

Here is how it is made:

• In cell G2, this formula will pick up the rank from C2 and return the name at that position in the list.
• In cell G3, it will pick the rank from C6 (which is 1 + 1*4, where 4 is the number of groups to be formed).
• In cell G4, it will pick the rank from C10 (which is 1 + 2*4, where 4 is the number of groups to be formed).

Since RANDBETWEEN function is volatile , it will automatically refresh every time you make a change in the worksheet. This may be undesirable as it will change the grouping every time.

To avoid this:

• Go to File Options.
• In the Excel Options dialog box, select formulas in the pane on the left.

Now the worksheet would not refresh until you force a refresh by hitting the F9 key.

But to make it look better, there is an orange button that does the refresh when you click it. There is a one-line VBA code at play here that gets executed whenever you click the button.

Here is how to insert this button:

• Close the VB Editor.

• Format the button the way you want.

Now when you click on the button, the worksheet would recalculate and you would get a new grouping based on the number of groups you have specified.

Other Excel Templates You May Like:

• Employee Leave/Vacation Tracker Template .
• Employee Timesheet Calculator .
• Excel To Do Lists Templates .
• A collection of FREE Excel Templates .

You may also like the following Excel tutorials:

• How to Generate Unique Random Numbers in Excel .
• How to Run a Macro in Excel .
• How to Create an Excel Dashboard .
• How to Rank within Groups in Excel
• How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas!

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

25 thoughts on “Random Group Generator Template [FREE Download]”

I need to add columns like, Last name, email address, Gender, City, and two more columns. Can you please guide me on how to work on the VBA so that I can use your template?

Is there any way to increase the max number of groups? I really need 15 groups! Please help… would be so great to use in my classroom.

I am sorry but the random team generator isn’t working correctly. It will pull duplicates about every 8th run. Conditional format for dups and you will see. Can it be fixed? I have trying but to no avail. Please advise. M

Thank you …i almost got here myself but couldn’t figure put how to add the names to the teams…awesome!

Thanks for the template! Is there a way to specify that I would want to have my samples distributed randomly across three groups (A,B,C) but have e.g. 80% of them in group-A, 10% in group-B and 10% in group-C?

Doesnt work, can anyone help. Why does the formual reference cell F if there is nothing in it?

You can help me to create a specific random match, is for a sport team, 6 teams with 3 members i need matching with rival teams and similar weight for the member or very close weight , please i pay you

This is a great tool. However I tried modifying it. But could’nt.

I just need 1 Team where I need to mention the no of members in cell E2 and it creates a team with Random Names. I am confident its a piece of cake for you.

Absolutely perfect but I need the absent function to work. I would love to this to randomly select 3 or 4 ball groups of golfers whilst we are on the tee. However we are never sure who is going to turn up hence the need to mark golfers absent from the master list and randomise the selection of the players who are there. Can anyone help???

Sorry this does not work very well. I have 40 teams and they have to be paired this goes no where needed for this program to work.

Hey there, Loved this! i ended up making a few alterations of my own, namely, put the generating function into VBA so that it stopped updating all the time, I added some arrows to increase/decrease the number of groups with a single click and also made a ‘secret’ tab where you could specify two people you didn’t want in the same group. I’d be happy to share it if you’re interested.

Hi Steve, Please do share how you worked on “secret” tab.

Is there a way to set group 1 to have 10 members, and the others to have like 7 ?

Cool trick, but i have one scenario. let’s say i have names(2 or more) that cannot be teamed together,is there any way to solve it?

Hi Sumit! This is amazing for my classroom thanks so much! Is there any way to increase the max number of groups? I really need 12 groups!

Dear Sumit,

I really enjoy using your random group generator for my classes (I created a tab for each class. It’s swift and easy to use.

Sometimes, however someone in a class is absent (visit to the dentist, etc.). If so, I have to alter the table, to remove the absent student.

It would be nice if there could be a column next to the student names where I could mark the absent student(s) (for example with a zero) , so he/she won’t be displayed in the generated groups.

Unfortunately I don’t have the programming skills to make that happen.

Someone else perhaps?

Greetings, Willem (Netherlands)

Thanks Willem.. Glad you liked it.

Here is a link to a new template that will allow you to mark a student as absent: https://www.dropbox.com/s/ys1mmwmbdy7eeb5/Random-Team-Generator-Template%20Absent.xlsm?dl=0

Hello… there is something wrong with the absent function in the worksheet…Even if I indicate that the student is absent, his name will still show in the generated teams list… Also, is there anyway to group 40 people into 2 groups… it seems that the template doesn’t support such a function… thanks and keep up the good work! 🙂

Hello, I was searching for an excel template to create random 4 man teams when I happened upon your template. I downloaded it and tested what I am trying to accomplish, but was having trouble when I found the above link. It gets me a step closer to what I am wanting to do, however, I am trying to accomplish the opposite. I have a list of 71, but I only want to include those I identify as opposed to excluding those that are identified. And I want the teams to be in multiples of 4, but I think if I identify the total number of teams, the template will provide me with teams of 3 or 4. So, I am wondering if a template is available that would provide the capability to include vs exclude? Thanks in advance for your help. Andy.

Thanks to both of you, Sumit and Rudra! Sumit, I enjoy your formulabased random generator. I have my ovn VBA-generator that I will continue using,but I learned a lot from your reallønn Nice formulas.

Rudra! I’ve never notised the easy way of changing calculation mode. I still miss a warning light when ExCeL is in manual mode. Forgetting to return to aut. Mode has caised me lot f extra work.

From the Custom Quick Access Toolbaar, add “Manual” to the Quick Access Toolbaar and it will show when in Manual mode.

Thanks Hennie, I’ve already adferd it to QAT.

Cool trick Sumit. Thanks for sharing. However there is a short cut to change calculation from ribbon itself. Just go to formula ribbon and click on Calculation Option.

Thanks for sharing Rudra.. That’s definitely the faster way to do this.

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Create random teams and groups easily

Keamk is a random team generator using different criteria such as skill level and gender of participants. No bias!

How it works?

Simple and easy to use. You can enjoy Keamk without registration.

Start by informing the elements of your draw: title, participants and teams. Then choose the type of your draw: regular, skill level or gender. Then let Keamk make your groups.

Use the different options available in the admin panel to manage your draw: modify, redo, duplicate or delete.

Share the result of a draw with the link of your public draw. You can also export it as an excel file and integrate the draw to any website with an embed code.

Get balanced teams based on skill level or gender

Skill Level

Assign a score from 1 to 5 to your participants and get balanced teams.

Select a gender for your participants and get balanced groups.

Keamk helps you in many domains

Get random and balanced teams for your sporting events.

Video Games

With friends or for e-sport, organize tournaments and games for your favorite video games.

Keamk helps teachers to create balanced groups in their classrooms.

Parties, special events, board games, escape games and more.

Do not waste your time anymore. Let chance decide for you.

Balanced teams and groups. Enjoy the power of our algorithms.

Draws on Keamk are anonymous, secure and private.

Mobile Friendly

Keamk automatically adapts to smartphones and tablets.

Share your draws directly on Facebook, Twitter and Reddit.

Export your teams and groups in Excel.

Express yourself. Leave your opinion about the result of a draw.

Keep track of your draws.

Save your participants and teams for later reuse.

Random Group Generator

Our random group generator template was created based on a request from somebody wanting to set up networking breakout sessions for a conference. As we researched the solution we found it had many applications in classrooms (e.g. seating charts and project groups), networking (e.g. lunch groups), and sports (randomizing teams). Although it is not specifically a solution to the social golfer problem (see below), it is a useful tool for generating random groups from a list of names.

License : Private Use (not for distribution or resale)

"No installation, no macros - just a simple spreadsheet"

How to Randomly Assign Groups or Teams

1. edit the list of names.

The Names worksheet is where you list the names of the people you want to assign to groups. You can include additional information as needed, similar to a roster or a sign-in sheet .

2. Sort the List of Names

Use the built-in Sort feature in Excel to sort by the RAND column, by the CUST ORDER column, alphabetically, or by any other sorting criteria that you want to use (e.g. skill level).

Every time you sort by the RAND column , the groups will be randomized.

If you want to preserve the order that you first entered the names, use the CUST ORDER column to enter 1,2,3,4..., then at any time you can sort by CUST ORDER to return them to the original order

3. Choose a Grouping Order

The list of names is either assigned to groups as 1,1,1,2,2,2,3,3,3,4,4,4... or 1,2,3,4,1,2,3,4,1,2,3,4... This means that after you have sorted the list of names using whatever sorting criteria you want to use, you have Two Unique Groupings to choose from (people are grouped differently in each).

4. Edit the Group Names

The number of groups is determined by the number of group names you include in the list. The number per group is determined automatically by the number of people and number of groups.

The Social Golfer Problem

Creating a randomized set of groups once is very simple, and this spreadsheet does it quite well. You can continue to sort the list by the RAND function every time you want to re-randomize the groups. But, what if you want to mix the groups again so that everybody is with a new group of people? How many times can you mix up the groups without anyone ever being with the same person more than once? That is the social golfer problem.

It's called "social" because the idea is to meet new people each time. This has application to education and business as well. In the classroom, you may want to assign class groups so that students are with a different set of students each time. At a training workshop you may want to create lunch groups over multiple days so that people can network with a different group of people each time.

Though the social golfer problem has been "solved" for specific numbers of people and group sizes, it is still considered an "unsolved" problem in general. If you are thinking "That's silly, how hard could it really be?" then I would encourage you to try it. It has stumped combinatorial mathematicians for a couple of decades so far.

We do not yet have a template for this type of social golfer problem.

• Social Golfer Problem at wikipedia.org - Although not exactly the same as wanting to create random groups, it is a similar problem.
• Breakout Group Allocation Schedules ... by Miller et al., https://doi.org/10.3390/sym13010013

Follow Us On ...

Schedules & Planners

• CELL REFERENCE

Post your problem and you'll get expert help in seconds

• All articles

Randomly Assign People to Groups in Excel

At times we have to randomly assign people to groups. This may seem a mind-numbing task, but it can be made very easy when executed in Excel.

How to randomly assign people to groups in Excel

Here we can use a formula created by the Excel RANK and ROUNDUP functions together. We also use a helper column where RAND function is used to randomly generate values.

To randomly assign people to groups we use the formula

=ROUNDUP(RANK(A1,randoms)/size,0)

The above formula returns a group no. for each entry.

‘ randoms ’ and ‘ size ’ are named range.

Random is a helper column generated by Excel RAND Function.

Explanation of formula

Excel RANK function is an inbuilt function in Excel which returns the rank of a number within a large set of numbers or array. This function is categorized as an inbuilt statistical function in Excel.

Excel ROUNDUP function is another inbuilt function in Excel which returns a number which is rounded up to a definite number of digits. This function rounds away from ‘0’. It is categorized as a Trig or Math function and can be aptly used as a worksheet function in Excel.

Let us take an example; we have to assign random groups to students for the aerobics class. We provide the size of group 4 in G10 as shown. To create groups, we use the formula shown in D5.

=ROUNDUP(RANK(C5,random)/size,0)

Here, “ random ” is the name of the range [C5:C20] and “ size ” is the named range [G10] .

We can drag the formula and use the above formula for a hefty amount of data.

We can also make use of the RAND function, which would generate random numbers. This function will keep on generating random values every time we change the values in the worksheet.

Alternate Solution

Another approach of assigning random value is by using the CEILING function which is another inbuilt function in Excel.

= CEILING ( RANK (C5,random)/size,1)

This function can be used as an alternative to the ROUNDUP function. The ceiling function rounds up, but instead of rounding to some given number of decimal places, it rounds up to a given multiple.

As per the example is shown in Figure 1, you can you use the above alternate formula to randomly assign people to groups as shown below.

Still need some help with Excel formatting or have other questions about Excel? Connect with a live  Excel expert  here for some 1 on 1 help. Your first session is always free. 

Did this post not answer your question? Get a solution from connecting with the expert.

Leave a Comment

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Did this post not answer your question? Get a solution from connecting with the expert

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.

Save time by asking instead! Most questions solved and answered in 10 minutes.

Random Team Generator Excel

Unleashing the power of a random team generator in excel.

Creating balanced and random teams can be a daunting task, especially when dealing with a large group of individuals. Whether it’s for a classroom activity, a sports league, or a corporate event, the need for a fair and unbiased team selection process is paramount. This is where the magic of Excel comes into play. With its robust functions and formulas, Excel can serve as an efficient random team generator, ensuring that teams are created quickly and fairly, without any hint of favoritism.

Understanding the Basics of Excel for Randomization

Before diving into the creation of a random team generator, it’s essential to grasp the fundamental Excel functions that make randomization possible. Excel offers a variety of functions that can be used to generate random numbers, shuffle lists, and ultimately create random teams.

• RAND : Generates a random number between 0 and 1.
• RANDBETWEEN : Returns a random integer between the numbers you specify.

These functions can be combined with other Excel features to create a dynamic and flexible random team generator. Let’s explore how to harness these functions to create your own random team generator in Excel.

Step-by-Step Guide to Building a Random Team Generator

Creating a random team generator in Excel involves several steps, from setting up your initial data to applying formulas for randomization. Here’s a comprehensive guide to get you started.

Setting Up Your Data

First, you’ll need to input the names of the participants into an Excel spreadsheet. Let’s assume you have a list of 20 participants. Enter each name into individual cells in a single column.

Assigning Random Numbers

Next to each participant’s name, use the  RANDBETWEEN  function to assign a random number. This will look something like this:

This formula will assign a random number between 1 and 1000 to each participant. The wide range ensures that the likelihood of two participants receiving the same number is minimal.

Sorting the List

Once you have assigned random numbers to each participant, you can sort the list based on these numbers. This shuffles the participants into a random order.

Dividing into Teams

After shuffling the participants, you can divide them into teams. If you want to create four teams, you can use the  MOD  function to assign a team number to each participant:

This formula will cycle through numbers 1 to 4 as you drag it down the list, effectively assigning each participant to one of four teams.

Finalizing the Teams

With team numbers assigned, you can then sort the list by team number to group participants together. You now have your randomly generated teams!

Advanced Techniques for Enhanced Randomization

While the basic method described above will work for most purposes, there are ways to enhance your random team generator for more complex scenarios.

Ensuring Even Skill Distribution

If you have information on each participant’s skill level, you can incorporate this into your randomization process to ensure teams are balanced. This might involve categorizing participants into skill tiers and then using the randomization process within each tier.

Accounting for Special Requirements

Sometimes, you may need to account for special requirements, such as ensuring an equal distribution of genders across teams or accommodating specific participant requests. This can be managed by adding additional constraints to your randomization formulas.

Automating the Process with Macros

For those who frequently need to generate random teams, automating the process with an Excel macro can save time and effort. By recording a macro of the steps above, you can create random teams with the click of a button.

Practical Applications of a Random Team Generator

The uses for a random team generator in Excel are vast and varied. Here are a few examples where such a tool can be invaluable:

• Organizing sports tournaments with fair and random team assignments.
• Creating balanced groups for classroom projects or activities.
• Facilitating networking events by randomly pairing attendees.

FAQ Section

Can excel’s random number functions generate duplicates.

Yes, since the functions are truly random, there is a possibility of generating duplicate numbers. However, using a large range with  RANDBETWEEN  minimizes this risk.

How can I prevent the random numbers from changing every time I make an edit?

To prevent the random numbers from recalculating, you can copy the cells with the random numbers and use “Paste Special” to paste them as values.

Is it possible to create teams with an uneven number of participants?

Yes, you can still divide participants into teams even if the numbers are uneven. The last team may just have one more or one fewer member.

A random team generator in Excel is a powerful tool that can simplify the process of creating fair and balanced teams. By leveraging Excel’s randomization functions and following the steps outlined in this guide, you can create a system that meets your specific needs, whether for educational, professional, or recreational purposes.

Remember, the key to a successful random team generator lies in understanding Excel’s capabilities and applying them creatively to your unique situation. With a bit of practice and experimentation, you’ll be able to generate random teams with ease, ensuring a fair and enjoyable experience for all participants.

• RAND Function – Microsoft Office Support
• RANDBETWEEN Function – Microsoft Office Support
• MOD Function – Microsoft Office Support

• Previous Excel Average Ignore Blanks
• Next How to Auto Format in Excel

Why Does My 4k Tv Not Look 4k

How To Remove Icloud Account From Mac Without Password

How To Reset Locked Iphone Without Icloud Password

Your email address will not be published. Required fields are marked *

• About JOE TECH
• Privacy Policy

Team Picker Wheel

Randomize people into groups

2. CONTROLLER

Tool settings.

Quick Tool Links: Picker Wheel , Yes No Picker Wheel , Number Picker Wheel , Letter Picker Wheel , Country Picker Wheel , Date Picker Wheel , Image Picker Wheel

Team Picker Wheel - Randomize a List of Names into Group

• What Is Team Picker Wheel?
• How to Use the Random Team Generator?
• Set Team Names
• Preset Group Members
• Tool Customization
• File Storage - How to Save/Open/Delete File?
• Create New Team and Switch Team List
• Modify Title Section
• Full Screen View
• How to Share Team Picker Wheel?
• Specification
• Random Group Generator Use Cases
• We Want to Hear Your Feedback

1. What Is Team Picker Wheel?

Team Picker Wheel is a random team generator developed by Picker Wheel team. It helps you to split a list of names into teams or groups. It is also known as a random group generator or can be used as a random pair generator.

By inserting the list of names into the team generator, the team generator will randomize all the names you entered into equal groups. You can set the number of groups or the number of people/group you want to create, generating equally into random groups.

There is another unique feature from this tool where you can choose to balance the gender of participants equally into groups, in the condition you have set the gender of each participant after filling in the names.

Besides, you don't need to download the groupings result manually from this group randomizer. You can save the group's result as an image or download the group's result in a CSV file for further use.

2. How to Use the Random Team Generator?

Insert participants' names (Two methods available).

Flip a coin to make a decision?

Try FlipSimu Coin Flipper->

Technology for Academics

Finding new technology so you don't have to

Random Team Generator: Excel File

I’ve been doing a lot more small group work in my courses. When I let students choose their own groups, they tend to gravitate to the people they know best. That means that they frequently get the same perspective over and over again. I decided to assign students to groups, but counting off in class is a bit of a pain – students get all settled in their seats, they count off, and then they have to pick up all of their gear and move. And I suspect there’s the occasional (frequent) trading of groups since I don’t remember who said which number.

Sumit Bansal created an Excel spreadsheet that will randomize students into groups. Visit his website and download the Excel template file . You can find the download link at the end of the very first section of text.

The random team generator is in the first and only worksheet of the file.

Since I’m creating small groups twice a week for two different classes, I added two worksheets to this file, one for each of my classes. These worksheets only contain the names of my students. If I know a student will be absent, I move them into an out-of-the way column. For the students I expect to be in class, I copy and paste their names into the “random team generator.” If there are more names than rows in the generator page, the generator page automatically does what it needs to do to include them. If there are fewer names than rows assigned by the generator, just delete the excess rows; if you don’t, you’ll have blank spots in your groups.

Now that you have your student names in the generator, enter the number of teams you want, and click the bright orange button. If you don’t like the grouping, just click the button again.

I copy and paste the names – okay, I use the Snipping Tool in Windows – into a document I can show in class. This document is the first thing I pull up on the classroom computer so students know where they need to be as soon as they walk in the door.

• Click to share on Facebook (Opens in new window)
• Click to share on Twitter (Opens in new window)
• Click to share on LinkedIn (Opens in new window)
• Click to print (Opens in new window)
• Click to email a link to a friend (Opens in new window)

3 thoughts on “ Random Team Generator: Excel File ”

I just copy names and paste them here: https://www.randomlists.com/team-generator

thank you!!!! very useful for off-line situations.

Nice Article

Comments are closed.

Random Team Generator:

How to create randomized groups.

Enter each item on a new line, choose the amount of groups unders settings, and click the button to generate your randomized list. Don't like the first team? Just click again until you do.

Fairly pick teams without bias. No need to draw names out of a hat. No need to do a grade school style draft or put hours of thought into the most balanced teams. The most fair dividing method possible is random.

Mix up your to-do list by generating random groups out of them. For example, enter all your housecleaning activities and split them into seven groups, one for each day or one for each person.

Want something similar?

Use the list randomizer if you don't want separate groups or use the random name picker to pull a single name.

• [email protected]
• +14372198199

How to Make Random Groups in Excel?

Random Groups in Excel can be a powerful tool for various applications, from statistical sampling to team assignments in a workplace setting. Creating these groups manually can be time-consuming and prone to bias, making Excel’s randomization functions a valuable asset. Whether you’re a researcher conducting a randomized control trial, a teacher assigning project groups, or a manager distributing tasks among teams, this guide will navigate you through the steps to efficiently generate random groups in Excel. By harnessing the power of Excel’s randomization features, you can ensure fairness and objectivity in your group assignments, while saving time and enhancing the overall efficiency of your organizational processes.

This Content Covers:

• Use of RANDBETWEEN Function
• Use of CHOOSE Function
• Use of combining RANDBETWEEN and CHOOSE Functions
• Explanation of formula
• CEILING Function
• Explanation of CEILING function
• Explanation with example

1. Make Random Groups in Excel

In certain situations, it may be desirable to generate groups for a list of names or randomly allocate data to groups. You can quickly create random groups for a set of data in Microsoft Excel by using a formula.

The RANDBETWEEN and CHOOSE Functions can be used to randomly allocate items (data, people, etc.) to groups.

Procedure of using RANDBETWEEN Function

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the RANDBETWEEN function, outlined in Red below.

After applying the function or formula, the result looks like below.

Step 2: After that, to fill all the cells, pull the “Fill Handle” further. The result is outlined in Red below.

• Use of CHOOSE Function:

Procedure of using CHOOSE Function:

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the CHOOSE function, outlined in Red below.

• Use of combination of RANDBETWEEN and CHOOSE Functions

Combining the two allows us to assign teams to groups by randomly “choosing” one item from a list.

Procedure of combining RANDBETWEEN and CHOOSE Functions:

Step 1: Next to the list you want to divide into random groups, choose a blank cell, then enter the function or formula, outlined in Red below.

2.Randomly assign people in the groups in Excel

Sometimes we have to pick groups at random. Although it could appear like a difficult undertaking, Excel makes it incredibly simple to complete by using formula.

Here, we may make use of a formula produced by combining the Excel RANK and ROUNDUP tools. Additionally, we employ a helper column where the RAND function is utilized to produce values at random.

We use the formula to assign individuals into groups at random.

=ROUNDUP(RANK(A1,randoms)/size,0)

For each element, the aforementioned formula returns a group number.

“size” and “randoms” are called range.

The Excel RAND function creates the helper column “Random”.

• Explanation of formula:

The Excel RANK function is a built-in feature that provides the position of a given number in an array or huge collection of numbers. In Excel, this function falls under the category of built-in statistical function.

Another built-in function in Excel is the ROUNDUP function, which returns a value that has been rounded to a predetermined number of digits. This function deviates from zero. It can be used effectively as an Excel worksheet function and is categorized as a Trig or Math function.

Procedure of assigning people in the groups randomly in Excel

Step 1: Let’s use a case where we have to assign teams to random groups. We apply the formula shown in D2 to build groups.

3. Alternative solution

The CEILING function, another built-in Excel function, can be used as an alternate method of assigning random values.

• CEILING Function:

We can use CEILING function or formula to assign individuals into groups at random.

=CEILING(RANK(C5,random)/size,1)

• Explanation of CEILING function:

In place of the ROUNDUP function, you can use this function. The ceiling function rounds up, but it does so to a specific multiple rather than to a specific number of decimal places.

• Explanation with example:

Procedure of assigning people in the groups randomly in Excel using CEILING function:

Here, “random” is the name of the range [C2:C13] and “size” is the named range[G5].

You may be interested:

• Financial Dashboards
• Sales Dashboards
• HR Dashboards
• Data Visualization Charts

Leave a Comment Cancel Reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

• Count Characters
• Count if method in Excel
• Print in Excel

Contact Info

Find us on Social Media

Our Address

273 Pharmacy Avenue Toronto, ON M1L3E9

© 2023 All rights reserved | Biz Infograph

Explore the new, free version of Kutools for Outlook today!

Quickly generate random groups for list of data in Excel

Sometimes, you may want to randomly assign data to groups as screenshot 1 shown, or generate groups for a list of names as below screenshot 2 shown, but how can handle these jobs quickly? Actually, in Excel, you can use formulas to solve them easily.

Randomly assign data to groups

Generate random groups in a specified data size, download sample file.

If you want to randomly assign data to a specified number of groups, each group is allowed with different numbers of data, you can use the CHOOSE and RANDBETWEEN functions.

Select a blank cell next to the list you want to assign to random groups, copy or type this formula

=CHOOSE(RANDBETWEEN(1,3),"Group A","Group B","Group C ")

In the formula, (1, 3) indicates to group data into 3 groups, Group A, Group B and Group C are the texts will be displayed in formula cells which used to distinguish different groups.

Then the list of data has been randomly assigned to groups, and each group may have different numbers of data.

The calculated results will not be fixed, they will be recalculated if there is any change to the workbook.

If you want to generate random groups for a list of data, and each group has a specified data size, you can use the ROUNDUP and RANK functions.

1. Firstly, you need a helper column to list some random data next to your data. Supposing in cell E2, type this formula

Then drag fill handle down to fill this formula to cells you use.

2. In the next column, supposing in cell F2, copy or type this formula

=ROUNDUP(RANK(E2,$E$2:$E$13)/4,0)

E2:E13 is the range that contains formula =RAND(), 4 is the number of data that you want each group contains.

Click to download sample file

Other Popular Articles

Remove first or last n characters from a cell or string in Excel

Extract part of text string from cell in Excel?

Two Easy Ways to convert or import Word document contents to Excel worksheet

Calculate the absolute difference between two values/times in Excel

More articles

The Best Office Productivity Tools

Kutools for excel solves most of your problems, and increases your productivity by 80%.

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range ...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average ... Prevent Duplicate Cells; Compare Ranges ...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets , Check Boxes and more...
• Favorite and Quickly Insert Formulas , Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text , Add Text, Remove by Position, Remove Space ; Create and Print Paging Subtotals; Convert Between Cells Content and Comments ...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets ; Merge Tables based on key columns; Split Data into Multiple Sheets ; Batch Convert xls, xlsx and PDF ...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name ...

Office Tab - brings tabbed interface to Office, and make your work much easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

• Forgotten Your Password?

• Today's Posts
• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel General
• [SOLVED] Random assigned teams with multiple criteria

Random assigned teams with multiple criteria

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible
• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode

Needing some help from those much smarter than I! I have attached a file for review. I have a list of students (Column B) who will be introducing another of their fellow students (Column E) who will then give a report to the class. All of the students are in a prearranged # of teams, and the number of students & teams will change from semester to semester. To assign the partners, I am using a Rand formula (Column A) to help with the random assignments. I then used an Index & Rank formula in Column E to come up with that random student name. I'm running into the problem of either students introducing themselves and/or are on the same team. What I am needing is a random partnering where students are 1) not introducing themselves, and 2) NOT on the same team. Essentially, the two 'Same' columns below should say 'NO'. Student Team Reporter Same? Team Same? Alice A Eric NO E NO Mary A Violet NO H NO Ben A Charles NO B NO Charles B Isaac NO E NO Rick B Lewis NO F NO Julie B Mary NO A NO Mike C Piper NO C YES Neil C Neil YES C YES Piper C Betty NO G NO Grace D Mike NO C NO Harold D Harold YES D YES Chris D Diana NO H NO Eric E Julie NO B NO Isaac E Sam NO G NO Susan E Susan YES E YES I've been combing through the boards for an answer but haven't quite found one that fits my needs and/or one(s) I can pick pieces from to come up with a solution. I'm probably making it much more complicated than it needs to be but sometimes us Excel geeks can't see the tree for the forest. Any help would be appreciated!

Last edited by mst3kr; 04-23-2015 at 02:43 PM .

Re: Random assigned teams with multiple criteria

I wasn't real certain how to attack this one. I may have made it more complicated than needed. How this works: There are two Named Ranges in Name Manager; they are MasterListTeam in columns AC:AD and MasterLookup in columns AE:AY. MasterLookup is formula driven. It creates row-wise lists of people that each corresponding person in MasterListTeam may introduce of course excluding themselves and fellow team members. It is an array formula that can be committed by simply hitting Enter, filling down and across. That has been done in the attached file already. That formula is Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } entered into AE4, filled down and across. The only column in the desired results table that does not have a formula is Presenters. Select that and the Rand Formula and sort on the Rand column. You will see the rest of that table re-shuffle without any "YES's" in columns "Presenting themselves" or "Same team". Those NO / YES's are driven by these formulas in columns O and Q. Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } and Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } The formula in column M determines the team that corresponding Presenter (column L) is on. It is Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } The formula in column P is similar and determines the team that corresponding Presenting (column N) is on. That formula is Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } The formula in column N determines who Presenter will be presenting. It uses the MasterLookup. The formula is Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); } While all this does what you've described above I couldn't help noticing that occasionally the same person is being introduced more than once. Is that OK? Edit Afterthoughts: That last formula is driven by RANDBETWEEN. I couldn't think of another way to do these assignments unless you introduce a helper column or some other device to conveniently replace RANDBETWEEN with some hard coded number. Other than that I suspect the only other way around the volatility of RANDBETWEEN (it recalculates and re-assigns every time you hit Enter and other stuff ) is to use a macro. Unfortunately I cannot do anything for you there. If you decide that is what you want to do request a Moderator move your thread there.......Please do not do this yourself. Another afterthought: As a non-VBA work around you could set your work book calculation to Manual. This is generally not a good practice if you are relying on accuracy of current data and have forgotten to manually recalculate. If you do decide to do this make sure that if other work books are going to be open at the same time you load this one last If you don't every work book you open subsequent to that will be set to calculate manually. It's one of Excel's hidden "gotcha's".

Last edited by FlameRetired; 04-23-2015 at 05:58 PM . Reason: After thoughts

Thanks for the quick & fantastic follow-up, Flame! It always amazes me when you talk with other Excel geeks just how much functionality there is in Excel that you know is there but just don't know how to fully take advantage of it! I understand & followed everything that you wrote, since you stepped though it so clearly. I, too, have had challenges with Rand & Randbetween formulas if I didn't have my calc turned to manual. After enough times, you learn not to forget! Nonetheless, thanks for the reminder! To answer your question (since I realize I didn't call it out in my post), each of the students should only be introduced once. So, somehow the column of 'Presenting' should obviously be the max # of students but only occur one time. Any help there?

Originally Posted by mst3kr To answer your question (since I realize I didn't call it out in my post), each of the students should only be introduced once. So, somehow the column of 'Presenting' should obviously be the max # of students but only occur one time. Any help there? You're welcome and glad it works to some extent. About the repeating students.....I've been wracking my brain unable to come up with anything. I need to sleep on this one. Edit Got it. No, it cannot be done. There are 24 rows to fill and only 21 students available if you wish to avoid same team members introducing one another. Sorry I hadn't noticed that before.

Last edited by FlameRetired; 04-23-2015 at 11:41 PM .

Thanks Flame! Now that I stop and think about it, you're absolutely right! In a way, we're taking 3 students out of the equation in terms of pairing everyone up to satisfy the teammate rule. So, you could only have 21 "clean" pairings while the other 3 would violate at least one of the two restrictions I wanted to put into place. I suppose I could get to my clean pairing of nobody introduces themselves and then manually move a few of the students around to get me as close as possible to satisfying both rules, knowing I will have some overlap in like teammates. Thank you very much for your help, Flame. You got me so much closer to my goal than I could've! And given the parameters I'm putting on the pairings, I think this is the best solution I could possibly hope for. So, it shall be put into use! Thanks again!

You are welcome. As a last try effort I put numbers 1-21 in column J (repeating 7, 14 and 21), replaced RANDBETWEEN with reference to column J then used the random equation column to shuffle column J. You will always get 3 repeaters no more and no less....but you will always have all 24 assigned. As an alternative leave out the duplicate 7, 14 and 21. You will get 3 rows of errors. (Perhaps some of your students could use a day off? )

Last edited by FlameRetired; 04-24-2015 at 06:57 PM .

Hi, I do these problems in a little different way. I used a lookup formula on the right and did a Rand() in Column A. Then sort by col A and copy names over to table on right. The team letter is a vlookup formula. I do it again and copy it to the second set of names on the right table. I do this using a real quick Macro Record. Then I use a formula to see if a person is presenting themselves and another column to see if they are presenting their own team. Now - Click on the top button and it will do the above and CF to show problems. Then I programmed a little loop to try 100 times to see if we can get to no present self or team member. It will stop if those conditions are met. See the attached and click some buttons. Don't look too hard at Macro 1 as it simply recorded it and didn't clean it up. I think I'm presenting a good possible sorting in the attached.

One test is worth a thousand opinions. Click the * Add Reputation below to say thanks.

@mst3kr My apologies. I was wrong! It can be done! I spoke too soon. and blush! I should have know better. @ MarvinP Thank you. When you're right you're right, and you are right. Thank you for correcting me.

Last edited by FlameRetired; 04-26-2015 at 12:53 PM .

I used helper columns (from I to AF) to display available presenting names for each presenter For example, number 4, presenter: Charles, team B, previous presenting : Will, Piper, Grace His presenting list: I4:AF4: excluding team B AND Will, Piper and Grace Problem is found for the last row, both can be the same team or same person! Originally Posted by mst3kr ... you could only have 21 "clean" pairings while the other 3 would violate at least one of the two restrictions I wanted to put into place... In fact, with my solution, there could be 24 "clean" pairing, in case of Name 1 vs Name 2 and Name 2 vs Name 1

Last edited by bebo021999; 04-26-2015 at 03:01 PM .

Sorry for the delay in responding... I knew there were much smarter folks than me out there that could help... I was able to use Marvin's suggestion and it worked perfectly!! Thank you all very much for the help! This is going to be extremely helpful to me in the future!

This is equivalent to a Secret Santa where nuclear family member do not gift one another. For example, Santa Family #Left Rand() Giftee Family Alan A 21 11 Nina E Barb A 20 8 Kent D Cain A 19 19 Xana H Dana B 18 5 Hana C Eric B 17 16 Vera H Fran B 16 12 Rene F Gary C 16 7 Jane D Hana C 15 11 Quin F Ivan C 14 10 Peri F Jane D 14 2 Barb A Kent D 13 2 Cain A Leah D 12 3 Eric B Mark E 10 5 Ivan C Nina E 9 3 Fran B Otto E 8 4 Leah D Peri F 9 2 Dana B Quin F 8 2 Gary C Rene F 7 7 Wade H Seth G 3 2 Mark E Tina G 2 1 Alan A Ulis G 1 1 Otto E Vera H 3 1 Seth G Wade H 2 2 Ulis G Xana H 1 1 Tina G There's a workbook that does this via formula at https://app.box.com/s/ug8etrfi404n3au5md5g

Entia non sunt multiplicanda sine necessitate

@ bebo021999 Really like your solution! Especially impressed by formulas you used to generate the lookup table. Very clever. BTW: I haven't had to F9 your workbook very many times to get all 24 clean pairings.....usually 1-3 times.

Last edited by FlameRetired; 04-29-2015 at 01:12 PM .

... deleted ...

Thread Information

Users browsing this thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

Random numbers assigned to static values multiple times no duplicates...., [solved] random team generator allowing duplicates on separate teams, set teams and random pairing of names., creating random teams, generate random teams, posting permissions.

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

• Site Search Search Posts Find A Forum Thread Number Threads by Name Search FAQs
• ENGINEERING.com
• Eng-Tips Forums
• Tek-Tips Forums

Join Tek-Tips ® Today!

Join your peers on the Internet's largest technical computer professional community. It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.

Join Us               Close

• Search forums
• Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

• If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
• Question Forums
• Excel Questions

How to get randomly assigned groups into equal amounts?

• Thread starter bwe89
• Start date Aug 24, 2017
• Tags assigning equal group groups randomly
• Aug 24, 2017

Hi everyone, I am using =CHOOSE(RANDBETWEEN(1,2),"A","B") to randomly assign data into two groups A & B. But when I do for 120 contacts does not group them into two equal groups of 60, which is what I want. Instead, it will have one group with 68 and the other with 52. Does anyone know a solution to make randomly assigning groups happen in equal quantities?  

Excel Facts

MrExcel MVP

Similar threads

• Feb 12, 2024

• Mar 31, 2023
• Jul 22, 2022
• Jun 24, 2024
• Apr 26, 2023

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock