Randomly Assign Your Excel Data into Groups

No video

Randomly Assign Your Excel Data into Groups

Рет қаралды 1,826

Күн бұрын

In this video, we explore multiple ways to generate random group assignments for our data. We will start with a shorter solution that requires two columns, but can cover any number of items in your data column. Next, we build a static, single-column solution that’s great when you know the number of datapoints. Lastly, we expand the single-column solution to end up with a single-column equation that can flex with the number of rows.
TIMESTAMPS:
====================
0:00 Introduction
0:20 Outline
1:14 Solution 1
4:07 Solution 2a
9:46 Solution 2b
12:42 Summary
12:42 Wrap up
EXCELSMITH LINKS:
====================
Unique Random Numbers (Not Excel 365): • Create a List of Uniqu...
Custom Array Using INDEX: • Create Custom Arrays U...
MICROSOFT LINKS:
====================
COUNTA: support.micros...
COUNTIF: support.micros...
IF: support.micros...
INDEX: support.micros...
LARGE: support.micros...
RANDBETWEEN: support.micros...
ROUNDUP: support.micros...
ROW: support.micros...
SUM: support.micros...
MICROSOFT KEYBOARD SHORTCUTS: support.micros...
UNSPLASH IMAGE:
====================
Photo by Mitchell Luo on Unsplash

Пікірлер: 6

@apekshasamyal 2 жыл бұрын

I have assigned people in group. What if one of the person gets in either group i want to highlight how do you do

@ExcelSmith 2 жыл бұрын

Thank you for the question. One way to highlight a person assigned to a particular group would be to use Conditional Formatting. For example, using the data in the video, the below Conditional Formatting formula would highlight “A. Huff” if they are in Group 1: Select column B -> Conditional Formatting -> New Rule -> Classic -> Use Formula: =AND($B1="A. Huff",$A1=1) Alternatively, you could highlight everyone that is in a particular Group, for example Group 1, using the below Conditional Formatting formula: Select column B -> Conditional Formatting -> New Rule -> Classic -> Use Formula: =$A1=1 I hope this helps. Thanks.

@JCEurovisionFan1996 2 жыл бұрын

How can I randomly assign data into groups for those who have Excel 365 and Excel 2021?

@ExcelSmith 2 жыл бұрын

Thanks for the question. The solutions in this video do work in Excel 365 and Excel 2021. However, these versions of Excel have new functions that make this easier. To create random group assignments using the new functions in Excel 365 and Excel 2021, we can expand on the solution covered in this video: kzfaq.info/get/bejne/ldugnNaG2M_Ip4U.html. For example, the below equation generates four random groups with three participants in each group: =ROUNDUP((SORTBY(SEQUENCE(12),RANDARRAY(12))/3),0)

@wat_herbivore1844 Жыл бұрын

good explanation but can't see your functions.

@ExcelSmith Жыл бұрын

Thank you for the comment and for the feedback. Here’s the final equation from the video at 9:48: =LARGE( ROW(INDEX($B:$B,1):INDEX($B:$B,ROUNDUP((COUNTA($B:$B)-1)/$E$1,0)))*IF(COUNTIF(A$1:A1,ROW(INDEX($B:$B,1):INDEX($B:$B,ROUNDUP((COUNTA($B:$B)-1)/$E$1,0))))=$E$1,0,1), RANDBETWEEN(1,ROUNDUP((COUNTA($B:$B)-1)/$E$1,0)-SUM(IF(COUNTIF($A$1:A1,ROW(INDEX($B:$B,1):INDEX($B:$B,ROUNDUP((COUNTA($B:$B)-1)/$E$1,0))))=$E$1,1,0))) ) This is an array formula which means it needs to be submitted by pressing Control + Shift + Enter. Thanks.