Grouping people by demographics and survey answers into 3 groups.

[Billbat]

Hello,
I have a group of 36 people and their demographics (Race, Age, Gender, Education, and votes) from a questionnaire they have filled out. I was wondering if there way a way to automatically disperse people into 3 groups of 12 in the order of Race, Gender, Age, and education so that we have diverse groups. I would then like to be able to make sure they're answers to a vote are not all the same as well.

I do this manually right now. But then have it going to a seating chart afterwards using indexing.

Name	Number	Age	Race	Gender	Education
Aubrey	1	20	Hispanic	F	Some College
Ben	2	59	White	M	Some College
Cheyenne	3	30	White	F	Some College
Christina	4	37	Asian	F	Some College
David	5	31	Hispanic	M	High School
Danny	6	60	White	M	College Grad
Dawn	7	49	Other	F	Post Grad
Denise	8	69	White	F	High School
Don	9	55	Hispanic	M	College Grad
Erica	10	37	Black	F	College Grad
Geo	11	30	Hispanic	F	Some College
Jamie	12	35	Asian	M	High School
Jennifer	13	41	Asian	F	Post Grad
Jethro	14	50	1/2 White, 1/2 Black	M	High School
Joe	15	67	White	M	College Grad
Jonna	16	53	White	F	College Grad
Juan	17	25	Hispanic	M	High School
Justin	18	36	White	M	College Grad
Kandi	19	47	Black	F	High School
Kathy	20	61	White	F	Some College
Kyle	21	33	White	M	Some College
Leonard	22	32	Black	M	Some College
Marci	23	51	White	F	High School
Mark	24	45	Black	M	College Grad
Melanie	25	28	White	F	College Grad
Michelle	26	58	White	F	Some College
Nancy	27	50	Black	F	High School
Nick	28	30	Hispanic	M	High School
Sam	29	18	Asian	M	High School
Viki	30	57	White	F	Post Grad

 

[Kaper]

So if I understand well, you want each group as uniform inside, and as different from other groups as possible. (if it is opposite and you wany grops as similar to each other and as a consequence as different inside gropp, then I'm more convinced by the solution, and the only difference will be that Minimum function shall be used below).

I'd use Solver for this task (If you don't know this tool check in microsoft help - it's preinstalled, but by default not active).

I'd initially assign some group 1-3 to each person. This assigned group would be later used as variable in Solver.

So for each group I'd calculate number of each race, each gender, each education, and each age group (I think it would be better than just dealing with age). This can be done with COUNTIFS and list of Unique values (gender, race, ...) can be produced with ... UNIQUE function.

The diversity between groups in each category can be assessed with standard deviation. and sum of all standard deviations can be treated as a measure or inter-group diversity.

So the data and calculations could look like this (see also results - they were extarcted from main table with FILTER function.
And below is the screenshot with SOLVER setup. Once again - if you want inner diversity within groups but small difference between groups choose minimum in solver goal).

losowanie zróżnicowanych grup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Name	Number	Age	Race	Gender	Education	Assigned group		Counter	1	2	3		Race	1	2	3			Gender	1	2	3			Education	1	2	3			Age	1	2	3
2	Aubrey	1	20	Hispanic	F	Some College	3			12	12	12		Hispanic	0	5	2	2,516611		F	10	2	7	4,041452		Some College	0	3	7	3,511885		30	1	6	3	2,516611
3	Ben	2	59	White	M	Some College	3							White	5	0	10	5		M	2	10	5	4,041452		High School	5	8	0	4,041452		45	4	5	1	2,081666
4	Cheyenne	3	30	White	F	Some College	3							Asian	1	4	0	2,081666								College Grad	5	1	4	2,081666		55	6	1	1	2,886751
5	Christina	4	37	Asian	F	Some College	2							Other	1	0	0	0,57735								Post Grad	2	0	1	1		70	1	0	7	3,785939
6	David	5	31	Hispanic	M	High School	2							Black	5	2	0	2,516611
7	Danny	6	60	White	M	College Grad	3							1/2 White, 1/2 Black	0	1	0	0,57735
8	Dawn	7	49	Other	F	Post Grad	1
9	Denise	8	69	White	F	High School	1							sum of std dev				13,26959						8,082904						10,635						11,27097
10	Don	9	55	Hispanic	M	College Grad	3
11	Erica	10	37	Black	F	College Grad	1		Aim: maximise for differences between groups					43,25846
12	Geo	11	30	Hispanic	F	Some College	2		or minimize to have inner diversity but low diversity between groups
13	Jamie	12	35	Asian	M	High School	2
14	Jennifer	13	41	Asian	F	Post Grad	1		Result
15	Jethro	14	50	1/2 White, 1/2 Black	M	High School	2		Group	1							Group	2							Group	3
16	Joe	15	67	White	M	College Grad	3		Name	Number	Age	Race	Gender	Education	Assigned group		Name	Number	Age	Race	Gender	Education	Assigned group		Name	Number	Age	Race	Gender	Education	Assigned group
17	Jonna	16	53	White	F	College Grad	1		Dawn	7	49	Other	F	Post Grad	1		Christina	4	37	Asian	F	Some College	2		Aubrey	1	20	Hispanic	F	Some College	3
18	Juan	17	25	Hispanic	M	High School	2		Denise	8	69	White	F	High School	1		David	5	31	Hispanic	M	High School	2		Ben	2	59	White	M	Some College	3
19	Justin	18	36	White	M	College Grad	1		Erica	10	37	Black	F	College Grad	1		Geo	11	30	Hispanic	F	Some College	2		Cheyenne	3	30	White	F	Some College	3
20	Kandi	19	47	Black	F	High School	1		Jennifer	13	41	Asian	F	Post Grad	1		Jamie	12	35	Asian	M	High School	2		Danny	6	60	White	M	College Grad	3
21	Kathy	20	61	White	F	Some College	3		Jonna	16	53	White	F	College Grad	1		Jethro	14	50	1/2 White, 1/2 Black	M	High School	2		Don	9	55	Hispanic	M	College Grad	3
22	Mark2	31	45	Black	M	College Grad	2		Justin	18	36	White	M	College Grad	1		Juan	17	25	Hispanic	M	High School	2		Joe	15	67	White	M	College Grad	3
23	Melanie2	32	28	White	F	College Grad	3		Kandi	19	47	Black	F	High School	1		Mark2	31	45	Black	M	College Grad	2		Kathy	20	61	White	F	Some College	3
24	Michelle2	33	58	White	F	Some College	3		Nancy2	34	50	Black	F	High School	1		Nick2	35	30	Hispanic	M	High School	2		Melanie2	32	28	White	F	College Grad	3
25	Nancy2	34	50	Black	F	High School	1		Marci	23	51	White	F	High School	1		Sam2	36	18	Asian	M	High School	2		Michelle2	33	58	White	F	Some College	3
26	Nick2	35	30	Hispanic	M	High School	2		Mark	24	45	Black	M	College Grad	1		Leonard	22	32	Black	M	Some College	2		Kyle	21	33	White	M	Some College	3
27	Sam2	36	18	Asian	M	High School	2		Melanie	25	28	White	F	College Grad	1		Nick	28	30	Hispanic	M	High School	2		Michelle	26	58	White	F	Some College	3
28	Kyle	21	33	White	M	Some College	3		Nancy	27	50	Black	F	High School	1		Sam	29	18	Asian	M	High School	2		Viki	30	57	White	F	Post Grad	3
29	Leonard	22	32	Black	M	Some College	2
30	Marci	23	51	White	F	High School	1
31	Mark	24	45	Black	M	College Grad	1
32	Melanie	25	28	White	F	College Grad	1
33	Michelle	26	58	White	F	Some College	3
34	Nancy	27	50	Black	F	High School	1
35	Nick	28	30	Hispanic	M	High School	2
36	Sam	29	18	Asian	M	High School	2
37	Viki	30	57	White	F	Post Grad	3
Sheet1

Cell Formulas
Range	Formula
J2:L2	J2	=COUNTIF($G$2:$G$37,J1)
N2:N7	N2	=UNIQUE(D2:D37)
O2:Q7	O2	=COUNTIFS($D$2:$D$37,$N2,$G$2:$G$37,O$1)
R2:R7,AJ2:AJ5,AD2:AD5,X2:X3	R2	=STDEV(O2:Q2)
T2:T3	T2	=UNIQUE(E2:E37)
U2:W3	U2	=COUNTIFS($E$2:$E$37,$T2,$G$2:$G$37,U$1)
Z2:Z5	Z2	=UNIQUE(F2:F37)
AA2:AC5	AA2	=COUNTIFS($F$2:$F$37,$Z2,$G$2:$G$37,AA$1)
AG2:AI2	AG2	=COUNTIFS($C$2:$C$37,"<="&$AF2,$G$2:$G$37,AG$1)
AG3:AI5	AG3	=COUNTIFS($C$2:$C$37,"<="&$AF3,$G$2:$G$37,AG$1)-SUM(AG$2:AG2)
R9,AJ9,AD9,X9	R9	=SUM(R2:R8)
N11	N11	=SUM(N9:AK9)
I17:O28,Y17:AE28,Q17:W28	I17	=FILTER($A2:$G37,$G2:$G37=J15)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$G$2:$G$37	Y17, Q17, I17, O2:Q7, AG2:AI5, U2:W3, J2:L2, AA2:AC5
solver_lhs1	=Sheet1!$G$2:$G$37	Y17, Q17, I17, O2:Q7, AG2:AI5, U2:W3, J2:L2, AA2:AC5
solver_lhs2	=Sheet1!$G$2:$G$37	Y17, Q17, I17, O2:Q7, AG2:AI5, U2:W3, J2:L2, AA2:AC5
solver_lhs3	=Sheet1!$G$2:$G$37	Y17, Q17, I17, O2:Q7, AG2:AI5, U2:W3, J2:L2, AA2:AC5

 

[Billbat]

Hi Kaper,
Thank you this is perfect.
Do you know if there is a way split groups evenly by priority?
For instance divide up Race as close to evenly as possible. Hispanic 2/2/3 White 5/5/5 Asian 2/1/2 Other 0/1/0 Black 3/2/2 and 1/2 White 0/1/0
Then for Gender try to get an even spread of Male and Female with Race being more important than Gender. Lastly trying to add in Education and age if possible.
I understand it is impossible to have everybody split perfectly because the sample is not going to be divisible by 3 every time.

This is the first time I'm using Solver and trying to understand it. I am using the Min to keep the groups at 12 people each.
If I'm understanding this correctly the solver parameters are set to the Sum of std dev. Anything below or equal to 3 is put into group 3, anything greater than or equal to 1 is put in group 2 and anything lower than 1 is turned into an integer 1. Then the counter is saying how many people go in each group?
What is actually changing the Assigned group that people are placed in?

Sorry if this is a lot to answer. Thanks again,
Billbat

 

[Kaper]

So for equal spread use Min as objective.
If you want to have equal spread of race being more important than gender equal spread, then multiply sum of race standard deviations by 2 (or may be even more. If it shall be really dominating, you may consider something like 10).
So change R9 formula to:

=2*SUM(R2:R8)

If you treat education or age as lower importance - you can add weight (multiplication by) of for instance 0.5 to these factors.

As for Solver action: you may initially assign all people to one group: write all the same numbers (from range 1..3) in column G.
When you start a Solver (clicking Solve), Solver tries to insert differnt values into these 36 cells and checks if these values first allow geting closer to fulfilling all constraints.
So at first it will focus on geting 12 1s, 12 2s and 12 3s in column G.
Then starts changing values between cells to keep 12 values of each group number, but distributing them that way, that they are as equally divided as possible.

Standard deviation, which i used here is a measure of a dispersion of data. So if we have 15 White - when 5 is assigned to each group standard deviation is 0. but if there is difference of 1 between one group and othr two groups, so
0 0 1, 0 1 1, 3 2 2, 3 3 2, 5 5 6, and so on, the standard deviation is the same in each of these cases (about 0.58)

While solver is running, you may see current value of the objective cell in status bar. It may be worth to give solver more time for next trials - you can do this in options in Solver window.
Another option is rerun the solver (without any changes done manually to column G). it may fing=d a better solution in the second (or may be even later) run.

In my case it was 3 runs in a row when I got such assignment to groups which is the best. And in case of these input data it will not change when we change the weights, because when any "features" can be divided by 3 (like 15 white persons and 3 postgrads) they are disrtibuted equally between groups. and in all other features there is one group with 1 (diffrence (more or less - it does not matter) than 2 other elements in this group.

losowanie zróżnicowanych grup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Name	Number	Age	Race	Gender	Education	Assigned group		Counter	1	2	3		Race	1	2	3			Gender	1	2	3			Education	1	2	3			Age	1	2	3
2	Jethro	14	50	1/2 White, 1/2 Black	M	High School	2			12	12	12		1/2 White, 1/2 Black	0	1	0	0,57735		M	6	5	6	0,57735		High School	4	5	4	0,57735		30	3	4	3	0,57735
3	Christina	4	37	Asian	F	Some College	1							Asian	1	2	2	0,57735		F	6	7	6	0,57735		Some College	4	3	3	0,57735		45	4	3	3	0,57735
4	Jennifer	13	41	Asian	F	Post Grad	2							Black	3	2	2	0,57735								Post Grad	1	1	1	0		55	2	3	3	0,57735
5	Jamie	12	35	Asian	M	High School	3							Hispanic	3	2	2	0,57735								College Grad	3	3	4	0,57735		70	3	2	3	0,57735
6	Sam2	36	18	Asian	M	High School	2							Other	0	0	1	0,57735
7	Sam	29	18	Asian	M	High School	3							White	5	5	5	0
8	Erica	10	37	Black	F	College Grad	3
9	Kandi	19	47	Black	F	High School	2							sum of std dev				2,886751						1,154701						1,732051						2,309401
10	Nancy2	34	50	Black	F	High School	1
11	Nancy	27	50	Black	F	High School	1		Aim: maximise for differences between groups					8,082904
12	Mark2	31	45	Black	M	College Grad	2		or minimize to have inner diversity but low diversity between groups
13	Leonard	22	32	Black	M	Some College	3
14	Mark	24	45	Black	M	College Grad	1		Result
15	Aubrey	1	20	Hispanic	F	Some College	1		Group	1							Group	2							Group	3
16	Geo	11	30	Hispanic	F	Some College	2		Name	Number	Age	Race	Gender	Education	Assigned group		Name	Number	Age	Race	Gender	Education	Assigned group		Name	Number	Age	Race	Gender	Education	Assigned group
17	David	5	31	Hispanic	M	High School	2		Christina	4	37	Asian	F	Some College	1		Jethro	14	50	1/2 White, 1/2 Black	M	High School	2		Jamie	12	35	Asian	M	High School	3
18	Don	9	55	Hispanic	M	College Grad	3		Nancy2	34	50	Black	F	High School	1		Jennifer	13	41	Asian	F	Post Grad	2		Sam	29	18	Asian	M	High School	3
19	Juan	17	25	Hispanic	M	High School	3		Nancy	27	50	Black	F	High School	1		Sam2	36	18	Asian	M	High School	2		Erica	10	37	Black	F	College Grad	3
20	Nick2	35	30	Hispanic	M	High School	1		Mark	24	45	Black	M	College Grad	1		Kandi	19	47	Black	F	High School	2		Leonard	22	32	Black	M	Some College	3
21	Nick	28	30	Hispanic	M	High School	1		Aubrey	1	20	Hispanic	F	Some College	1		Mark2	31	45	Black	M	College Grad	2		Don	9	55	Hispanic	M	College Grad	3
22	Dawn	7	49	Other	F	Post Grad	3		Nick2	35	30	Hispanic	M	High School	1		Geo	11	30	Hispanic	F	Some College	2		Juan	17	25	Hispanic	M	High School	3
23	Cheyenne	3	30	White	F	Some College	3		Nick	28	30	Hispanic	M	High School	1		David	5	31	Hispanic	M	High School	2		Dawn	7	49	Other	F	Post Grad	3
24	Denise	8	69	White	F	High School	3		Michelle2	33	58	White	F	Some College	1		Melanie2	32	28	White	F	College Grad	2		Cheyenne	3	30	White	F	Some College	3
25	Jonna	16	53	White	F	College Grad	3		Viki	30	57	White	F	Post Grad	1		Marci	23	51	White	F	High School	2		Denise	8	69	White	F	High School	3
26	Kathy	20	61	White	F	Some College	3		Joe	15	67	White	M	College Grad	1		Melanie	25	28	White	F	College Grad	2		Jonna	16	53	White	F	College Grad	3
27	Melanie2	32	28	White	F	College Grad	2		Justin	18	36	White	M	College Grad	1		Michelle	26	58	White	F	Some College	2		Kathy	20	61	White	F	Some College	3
28	Michelle2	33	58	White	F	Some College	1		Kyle	21	33	White	M	Some College	1		Ben	2	59	White	M	Some College	2		Danny	6	60	White	M	College Grad	3
29	Marci	23	51	White	F	High School	2
30	Melanie	25	28	White	F	College Grad	2
31	Michelle	26	58	White	F	Some College	2
32	Viki	30	57	White	F	Post Grad	1
33	Ben	2	59	White	M	Some College	2
34	Danny	6	60	White	M	College Grad	3
35	Joe	15	67	White	M	College Grad	1
36	Justin	18	36	White	M	College Grad	1
37	Kyle	21	33	White	M	Some College	1
Sheet1

Cell Formulas
Range	Formula
J2:L2	J2	=COUNTIF($G$2:$G$37,J1)
N2:N7	N2	=UNIQUE(D2:D37)
O2:Q7	O2	=COUNTIFS($D$2:$D$37,$N2,$G$2:$G$37,O$1)
R2:R7,AJ2:AJ5,AD2:AD5,X2:X3	R2	=STDEV(O2:Q2)
T2:T3	T2	=UNIQUE(E2:E37)
U2:W3	U2	=COUNTIFS($E$2:$E$37,$T2,$G$2:$G$37,U$1)
Z2:Z5	Z2	=UNIQUE(F2:F37)
AA2:AC5	AA2	=COUNTIFS($F$2:$F$37,$Z2,$G$2:$G$37,AA$1)
AG2:AI2	AG2	=COUNTIFS($C$2:$C$37,"<="&$AF2,$G$2:$G$37,AG$1)
AG3:AI5	AG3	=COUNTIFS($C$2:$C$37,"<="&$AF3,$G$2:$G$37,AG$1)-SUM(AG$2:AG2)
R9,AJ9,AD9,X9	R9	=SUM(R2:R8)
N11	N11	=SUM(N9:AK9)
I17:O28,Y17:AE28,Q17:W28	I17	=FILTER($A2:$G37,$G2:$G37=J15)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet1!$G$2:$G$37	Y17, Q17, I17, AG2:AI5, AA2:AC5, U2:W3, O2:Q7, J2:L2
solver_lhs1	=Sheet1!$G$2:$G$37	Y17, Q17, I17, AG2:AI5, AA2:AC5, U2:W3, O2:Q7, J2:L2
solver_lhs2	=Sheet1!$G$2:$G$37	Y17, Q17, I17, AG2:AI5, AA2:AC5, U2:W3, O2:Q7, J2:L2
solver_lhs3	=Sheet1!$G$2:$G$37	Y17, Q17, I17, AG2:AI5, AA2:AC5, U2:W3, O2:Q7, J2:L2

 

[Billbat]

Thank you so much this makes perfect sense now and I feel like I understand it.

Billbat

 

[Kaper]

Glad to hear that. If you feel it closes the case, you may mark post#4 as a solution: Mark as Solution