Random generate multiple names from a list with exclusions & sorting

[camspy]

Here's my table:

Here's also the sheet:

test.xlsx
	A	B	C	D	E	F
1	Names	Rank	Exclude	simple	sorted	sorted+excluded
2	Abigail	65	Olivia
3	Sophia	25	Mila
4	Amelia	30
5	Elizabeth	80	Isabella,Sophia,Amelia
6	Ava	15	Gianna,Emma
7	Isabella	35	Luna
8	Gianna	60
9	Harper	50
10	Mila	100	Sofia
11	Olivia	5
12	Charlotte	20
13	Evelyn	45	Camila,Ava,Abigail
14	Avery	95	Charlotte
15	Emily	90	Evelyn,Mia
16	Ella	75
17	Sofia	85	Avery,Emily
18	Mia	40	Elizabeth,Ella
19	Camila	55
20	Emma	10	Harper
21	Luna	70
Sheet1

I need to generate 10 random names (delimited), without duplicates, in each cell of columns D, E and F, the rules are below:

1. In column D: just the list of 10 randomly generated and delimited names, without dupes;
2. In column E: the same as in column D, but the results should be sorted by ranks taken from B2:B21, from high to low;
3. In column F: the same as in column E, but the results will have per-row exclusions, listed in column C.

Each result should contain 10 names, and visually should look like this: Sophia,Amelia,Isabella,Mia,Evelyn,Harper,Camila,Gianna,Abigail,Luna

I have tried many of the solutions with RANDARRAY and RANDBETWEEN formulas found on the internet, but didn't succeed myself.

Maybe it's not even possible to have it working using just formulas?
Maybe VBA is required?

Any help is appreciated.
Thanks in advance.

 

[Fluff]

Ok, did some testing, put the col F formula into cells D24:BA24 & counted how many times each name occurred & ran the calculation 22 times & these are the results.

+Fluff 1.xlsm
	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
24				Avery	Abigail	Elizabeth	Mila	Mila	Avery	Mila	Mila	Avery	Emily	Emily	Avery	Emily	Avery	Avery	Mila	Avery	Emily	Mila	Mila	Emily	Mila
25				Emily	Gianna	Ella	Sofia	Emily	Ella	Emily	Sofia	Ella	Sofia	Sofia	Emily	Sofia	Ella	Sofia	Emily	Sofia	Sofia	Avery	Elizabeth	Sofia	Sofia
26				Sofia	Harper	Gianna	Luna	Sofia	Luna	Ella	Elizabeth	Abigail	Ella	Abigail	Sofia	Ella	Luna	Ella	Gianna	Luna	Elizabeth	Sofia	Ella	Elizabeth	Ella
27				Ella	Evelyn	Mia	Harper	Elizabeth	Abigail	Abigail	Luna	Camila	Luna	Gianna	Camila	Luna	Abigail	Luna	Camila	Abigail	Abigail	Elizabeth	Abigail	Abigail	Camila
28				Abigail	Isabella	Isabella	Mia	Gianna	Gianna	Gianna	Gianna	Evelyn	Camila	Harper	Harper	Abigail	Gianna	Abigail	Evelyn	Harper	Camila	Abigail	Evelyn	Camila	Harper
29				Gianna	Amelia	Amelia	Isabella	Camila	Camila	Camila	Isabella	Amelia	Evelyn	Evelyn	Mia	Harper	Evelyn	Gianna	Mia	Evelyn	Amelia	Camila	Isabella	Isabella	Evelyn
30				Camila	Charlotte	Sophia	Sophia	Evelyn	Evelyn	Isabella	Amelia	Sophia	Mia	Mia	Sophia	Isabella	Amelia	Charlotte	Isabella	Mia	Charlotte	Isabella	Amelia	Charlotte	Amelia
31				Mia	Ava	Charlotte	Charlotte	Isabella	Isabella	Sophia	Sophia	Charlotte	Isabella	Sophia	Charlotte	Sophia	Sophia	Ava	Charlotte	Isabella	Ava	Charlotte	Charlotte	Ava	Charlotte
32				Amelia	Emma	Ava	Emma	Amelia	Sophia	Emma	Emma	Emma	Amelia	Ava	Emma	Charlotte	Ava	Emma	Emma	Amelia	Emma	Ava	Ava	Emma	Emma
33				Emma	Olivia	Emma	Olivia	Charlotte	Charlotte	Olivia	Olivia	Olivia	Emma	Emma	Olivia	Ava	Olivia	Olivia	Olivia	Charlotte	Olivia	Olivia	Emma	Olivia	Olivia
34
35	Abigail		31	1	1	0	0	0	1	1	0	1	0	1	0	1	1	1	0	1	1	1	1	1	0
36	Sophia		20	0	0	1	1	0	1	1	1	1	0	1	1	1	1	0	0	0	0	0	0	0	0
37	Amelia		27	1	1	1	0	1	0	0	1	1	1	0	0	0	1	0	0	1	1	0	1	0	1
38	Elizabeth		24	0	0	1	0	1	0	0	1	0	0	0	0	0	0	0	0	0	1	1	1	1	0
39	Ava		26	0	1	1	0	0	0	0	0	0	0	1	0	1	1	1	0	0	1	1	1	1	0
40	Isabella		29	0	1	1	1	1	1	1	1	0	1	0	0	1	0	0	1	1	0	1	1	1	0
41	Gianna		26	1	1	1	0	1	1	1	1	0	0	1	0	0	1	1	1	0	0	0	0	0	0
42	Harper		20	0	1	0	1	0	0	0	0	0	0	1	1	1	0	0	0	1	0	0	0	0	1
43	Mila		21	0	0	0	1	1	0	1	1	0	0	0	0	0	0	0	1	0	0	1	1	0	1
44	Olivia		26	0	1	0	1	0	0	1	1	1	0	0	1	0	1	1	1	0	1	1	0	1	1
45	Charlotte		33	0	1	1	1	1	1	0	0	1	0	0	1	1	0	1	1	1	1	1	1	1	1
46	Evelyn		20	0	1	0	0	1	1	0	0	1	1	1	0	0	1	0	1	1	0	0	1	0	1
47	Avery		19	1	0	0	0	0	1	0	0	1	0	0	1	0	1	1	0	1	0	1	0	0	0
48	Emily		23	1	0	0	0	1	0	1	0	0	1	1	1	1	0	0	1	0	1	0	0	1	0
49	Ella		25	1	0	1	0	0	1	1	0	1	1	0	0	1	1	1	0	0	0	0	1	0	1
50	Sofia		32	1	0	0	1	1	0	0	1	0	1	1	1	1	0	1	0	1	1	1	0	1	1
51	Mia		19	1	0	1	1	0	0	0	0	0	1	1	1	0	0	0	1	1	0	0	0	0	0
52	Camila		26	1	0	0	0	1	1	1	0	1	1	0	1	0	0	0	1	0	1	1	0	1	1
53	Emma		32	1	1	1	1	0	0	1	1	1	1	1	1	0	0	1	1	0	1	0	1	1	1
54	Luna		21	0	0	0	1	0	1	0	1	0	1	0	0	1	1	1	0	1	0	0	0	0	0
55
56	Abigail	28	25	28	21	26	25	17	25	29	27	25	25	25	26	26	24	23	27	24	21	22	20	24.50	23.68
57	Sophia	28	28	24	29	24	24	25	24	28	23	22	21	26	23	22	23	25	22	24	26	22	27	24.55	26.36
58	Amelia	14	25	28	28	24	23	28	20	31	23	25	22	27	23	24	29	30	21	28	24	24	26	24.86
59	Elizabeth	25	25	24	22	24	21	25	29	21	27	22	27	25	24	28	25	25	30	22	28	26	27	25.09	14
60	Ava	25	22	18	26	23	23	23	25	30	19	27	25	22	29	31	23	22	32	21	26	31	26	24.95	40
61	Isabella	24	24	22	28	22	26	28	20	22	20	24	32	23	31	23	30	28	27	26	21	25	27	25.14
62	Gianna	23	28	37	24	18	24	31	31	29	27	20	22	22	26	26	23	33	19	27	22	22	24	25.36
63	Harper	17	23	26	23	21	26	21	29	26	30	30	26	26	19	19	22	27	29	25	29	30	25	24.95
64	Mila	28	29	21	27	28	25	20	15	22	32	26	27	34	23	29	21	27	27	30	24	26	21	25.55
65	Olivia	29	19	25	32	24	25	27	20	20	26	26	23	29	27	21	24	26	19	22	30	23	23	24.55
66	Charlotte	24	23	28	26	22	28	27	26	21	24	26	28	23	23	23	31	28	29	27	24	24	29	25.64
67	Evelyn	25	25	21	24	27	27	30	26	17	28	27	29	24	23	23	26	30	26	25	24	22	21	25.00
68	Avery	29	25	24	24	25	23	21	27	18	27	22	18	28	20	29	24	23	23	25	29	30	24	24.45
69	Emily	26	24	28	25	27	23	28	28	22	24	24	26	26	23	24	24	23	28	18	31	26	23	25.05
70	Ella	25	27	23	23	27	24	19	18	27	26	26	26	24	26	25	25	15	26	24	21	18	26	23.68
71	Sofia	29	26	25	30	28	25	29	34	24	24	29	30	21	25	27	30	22	19	26	28	25	24	26.36
72	Mia	26	23	23	20	28	25	24	28	29	31	32	24	30	40	21	24	28	21	28	17	22	28	26.00
73	Camila	24	29	22	22	25	21	25	26	25	21	18	25	19	23	26	24	18	24	25	24	27	29	23.73
74	Emma	22	29	23	26	27	37	28	23	31	23	22	22	22	25	26	28	24	29	28	27	32	25	26.32
75	Luna	29	21	30	20	30	25	24	26	28	18	27	22	24	21	27	20	23	22	25	24	23	25	24.27
Main

Cell Formulas
Range	Formula
D24:Y33	D24	=LET(Ex,FILTERXML("<k><m>"&SUBSTITUTE(C2,",","</m><m>")&"</m></k>","//m"),f,FILTER($A$2:$B$21,ISNA(MATCH($A$2:$A$21,IF(COUNTA(Ex)=1,C2,Ex),0))),INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1))
D35:Y54	D35	=COUNTIFS(D24#,$A$2:$A$21)
C35:C54	C35	=SUM(D35:BA35)
Y56	Y56	=MIN(X56:X75)
Y57	Y57	=MAX(X56:X75)
Y59	Y59	=MIN(B56:W75)
Y60	Y60	=MAX(B56:W75)
X56:X75	X56	=AVERAGE($B56:W56)
Dynamic array formulas.

Whilst the difference between the Min & Max was a bit more than I expected, the average number of times a name occured was between 23.68 & 26.36, which is what I would expect.

 

[camspy]

Ok, did some testing, put the col F formula into cells D24:BA24 & counted how many times each name occurred & ran the calculation 22 times & these are the results.

+Fluff 1.xlsm
	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
24				Avery	Abigail	Elizabeth	Mila	Mila	Avery	Mila	Mila	Avery	Emily	Emily	Avery	Emily	Avery	Avery	Mila	Avery	Emily	Mila	Mila	Emily	Mila
25				Emily	Gianna	Ella	Sofia	Emily	Ella	Emily	Sofia	Ella	Sofia	Sofia	Emily	Sofia	Ella	Sofia	Emily	Sofia	Sofia	Avery	Elizabeth	Sofia	Sofia
26				Sofia	Harper	Gianna	Luna	Sofia	Luna	Ella	Elizabeth	Abigail	Ella	Abigail	Sofia	Ella	Luna	Ella	Gianna	Luna	Elizabeth	Sofia	Ella	Elizabeth	Ella
27				Ella	Evelyn	Mia	Harper	Elizabeth	Abigail	Abigail	Luna	Camila	Luna	Gianna	Camila	Luna	Abigail	Luna	Camila	Abigail	Abigail	Elizabeth	Abigail	Abigail	Camila
28				Abigail	Isabella	Isabella	Mia	Gianna	Gianna	Gianna	Gianna	Evelyn	Camila	Harper	Harper	Abigail	Gianna	Abigail	Evelyn	Harper	Camila	Abigail	Evelyn	Camila	Harper
29				Gianna	Amelia	Amelia	Isabella	Camila	Camila	Camila	Isabella	Amelia	Evelyn	Evelyn	Mia	Harper	Evelyn	Gianna	Mia	Evelyn	Amelia	Camila	Isabella	Isabella	Evelyn
30				Camila	Charlotte	Sophia	Sophia	Evelyn	Evelyn	Isabella	Amelia	Sophia	Mia	Mia	Sophia	Isabella	Amelia	Charlotte	Isabella	Mia	Charlotte	Isabella	Amelia	Charlotte	Amelia
31				Mia	Ava	Charlotte	Charlotte	Isabella	Isabella	Sophia	Sophia	Charlotte	Isabella	Sophia	Charlotte	Sophia	Sophia	Ava	Charlotte	Isabella	Ava	Charlotte	Charlotte	Ava	Charlotte
32				Amelia	Emma	Ava	Emma	Amelia	Sophia	Emma	Emma	Emma	Amelia	Ava	Emma	Charlotte	Ava	Emma	Emma	Amelia	Emma	Ava	Ava	Emma	Emma
33				Emma	Olivia	Emma	Olivia	Charlotte	Charlotte	Olivia	Olivia	Olivia	Emma	Emma	Olivia	Ava	Olivia	Olivia	Olivia	Charlotte	Olivia	Olivia	Emma	Olivia	Olivia
34
35	Abigail		31	1	1	0	0	0	1	1	0	1	0	1	0	1	1	1	0	1	1	1	1	1	0
36	Sophia		20	0	0	1	1	0	1	1	1	1	0	1	1	1	1	0	0	0	0	0	0	0	0
37	Amelia		27	1	1	1	0	1	0	0	1	1	1	0	0	0	1	0	0	1	1	0	1	0	1
38	Elizabeth		24	0	0	1	0	1	0	0	1	0	0	0	0	0	0	0	0	0	1	1	1	1	0
39	Ava		26	0	1	1	0	0	0	0	0	0	0	1	0	1	1	1	0	0	1	1	1	1	0
40	Isabella		29	0	1	1	1	1	1	1	1	0	1	0	0	1	0	0	1	1	0	1	1	1	0
41	Gianna		26	1	1	1	0	1	1	1	1	0	0	1	0	0	1	1	1	0	0	0	0	0	0
42	Harper		20	0	1	0	1	0	0	0	0	0	0	1	1	1	0	0	0	1	0	0	0	0	1
43	Mila		21	0	0	0	1	1	0	1	1	0	0	0	0	0	0	0	1	0	0	1	1	0	1
44	Olivia		26	0	1	0	1	0	0	1	1	1	0	0	1	0	1	1	1	0	1	1	0	1	1
45	Charlotte		33	0	1	1	1	1	1	0	0	1	0	0	1	1	0	1	1	1	1	1	1	1	1
46	Evelyn		20	0	1	0	0	1	1	0	0	1	1	1	0	0	1	0	1	1	0	0	1	0	1
47	Avery		19	1	0	0	0	0	1	0	0	1	0	0	1	0	1	1	0	1	0	1	0	0	0
48	Emily		23	1	0	0	0	1	0	1	0	0	1	1	1	1	0	0	1	0	1	0	0	1	0
49	Ella		25	1	0	1	0	0	1	1	0	1	1	0	0	1	1	1	0	0	0	0	1	0	1
50	Sofia		32	1	0	0	1	1	0	0	1	0	1	1	1	1	0	1	0	1	1	1	0	1	1
51	Mia		19	1	0	1	1	0	0	0	0	0	1	1	1	0	0	0	1	1	0	0	0	0	0
52	Camila		26	1	0	0	0	1	1	1	0	1	1	0	1	0	0	0	1	0	1	1	0	1	1
53	Emma		32	1	1	1	1	0	0	1	1	1	1	1	1	0	0	1	1	0	1	0	1	1	1
54	Luna		21	0	0	0	1	0	1	0	1	0	1	0	0	1	1	1	0	1	0	0	0	0	0
55
56	Abigail	28	25	28	21	26	25	17	25	29	27	25	25	25	26	26	24	23	27	24	21	22	20	24.50	23.68
57	Sophia	28	28	24	29	24	24	25	24	28	23	22	21	26	23	22	23	25	22	24	26	22	27	24.55	26.36
58	Amelia	14	25	28	28	24	23	28	20	31	23	25	22	27	23	24	29	30	21	28	24	24	26	24.86
59	Elizabeth	25	25	24	22	24	21	25	29	21	27	22	27	25	24	28	25	25	30	22	28	26	27	25.09	14
60	Ava	25	22	18	26	23	23	23	25	30	19	27	25	22	29	31	23	22	32	21	26	31	26	24.95	40
61	Isabella	24	24	22	28	22	26	28	20	22	20	24	32	23	31	23	30	28	27	26	21	25	27	25.14
62	Gianna	23	28	37	24	18	24	31	31	29	27	20	22	22	26	26	23	33	19	27	22	22	24	25.36
63	Harper	17	23	26	23	21	26	21	29	26	30	30	26	26	19	19	22	27	29	25	29	30	25	24.95
64	Mila	28	29	21	27	28	25	20	15	22	32	26	27	34	23	29	21	27	27	30	24	26	21	25.55
65	Olivia	29	19	25	32	24	25	27	20	20	26	26	23	29	27	21	24	26	19	22	30	23	23	24.55
66	Charlotte	24	23	28	26	22	28	27	26	21	24	26	28	23	23	23	31	28	29	27	24	24	29	25.64
67	Evelyn	25	25	21	24	27	27	30	26	17	28	27	29	24	23	23	26	30	26	25	24	22	21	25.00
68	Avery	29	25	24	24	25	23	21	27	18	27	22	18	28	20	29	24	23	23	25	29	30	24	24.45
69	Emily	26	24	28	25	27	23	28	28	22	24	24	26	26	23	24	24	23	28	18	31	26	23	25.05
70	Ella	25	27	23	23	27	24	19	18	27	26	26	26	24	26	25	25	15	26	24	21	18	26	23.68
71	Sofia	29	26	25	30	28	25	29	34	24	24	29	30	21	25	27	30	22	19	26	28	25	24	26.36
72	Mia	26	23	23	20	28	25	24	28	29	31	32	24	30	40	21	24	28	21	28	17	22	28	26.00
73	Camila	24	29	22	22	25	21	25	26	25	21	18	25	19	23	26	24	18	24	25	24	27	29	23.73
74	Emma	22	29	23	26	27	37	28	23	31	23	22	22	22	25	26	28	24	29	28	27	32	25	26.32
75	Luna	29	21	30	20	30	25	24	26	28	18	27	22	24	21	27	20	23	22	25	24	23	25	24.27
Main

Cell Formulas
Range	Formula
D24:Y33	D24	=LET(Ex,FILTERXML("<k><m>"&SUBSTITUTE(C2,",","</m><m>")&"</m></k>","//m"),f,FILTER($A$2:$B$21,ISNA(MATCH($A$2:$A$21,IF(COUNTA(Ex)=1,C2,Ex),0))),INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1))
D35:Y54	D35	=COUNTIFS(D24#,$A$2:$A$21)
C35:C54	C35	=SUM(D35:BA35)
Y56	Y56	=MIN(X56:X75)
Y57	Y57	=MAX(X56:X75)
Y59	Y59	=MIN(B56:W75)
Y60	Y60	=MAX(B56:W75)
X56:X75	X56	=AVERAGE($B56:W56)
Dynamic array formulas.

Whilst the difference between the Min & Max was a bit more than I expected, the average number of times a name occured was between 23.68 & 26.36, which is what I would expect.

Thank you so much for your assistance.

I did a test once again too.

My data has 254 rows, 254 names, I generate 5 names per row. I exclude 1 name in each row.
I can't publish my data, but just for comparison, look at the extreme values:

1 name appeard 14 times
1 name appeard 11 times
6 names appeard 10 times
---
9 names appeard 1 times
15 names appeard 2 times
35 names appeard 3 times

Since I get the results generated all the time, I usually see some names appearing up to 40 times while others get 0 shows.
So I wonder...

 

[Fluff]

I've run tests on that number of rows & names with 25 runs the min was 0 & the max 14 & each name appeared between 96 & 158
Considering you are picking 5 names out of 254, I would say that was about right.

 

[camspy]

Thank you Fluff. If you have any idea - please let me know.

 

[Fluff]

Any idea about what?

 

[camspy]

About how to get the more even distribution thing solved )

 

[Fluff]

Fraid not. As I said with 5 names out of 245 getting a range of 0 to 14 is about what I would expect.

 

[camspy]

I see.
Well, maybe there are other modifiers that may be added to the formula, that would let's say give more chance for higher rank names to be generated, and lower chance for lower rank names?

Like 30% of names from a higher rank end would get a boost and 30% of lower rank would get less if a chance...