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]

Hi & welcome to MrExcel.
Should cols E & F have the same names as col D, or should they be another set of random names?

 

[camspy]

Hi & welcome to MrExcel.
Should cols E & F have the same names as col D, or should they be another set of random names?

Hi Fluff.

Cols E & F can have the same names as col D, but because col F will have some exclusions, it needs some additional names added that were absent in cols D & E, to still have 10 names...
They can also have another set of random names.

 

[Fluff]

Ok, this will do cols D & E

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

Cell Formulas
Range	Formula
D2:D21	D2	=TEXTJOIN(", ",,INDEX(SORTBY($A$2:$A$21,RANDARRAY(20)),SEQUENCE(10)))
E2:E21	E2	=TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY($A$2:$B$21,RANDARRAY(20)),SEQUENCE(10),{1,2}),2,-1),0,1))

Am working on F

 

[Fluff]

With col F as well

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

Cell Formulas
Range	Formula
D2:D21	D2	=TEXTJOIN(", ",,INDEX(SORTBY($A$2:$A$21,RANDARRAY(20)),SEQUENCE(10)))
E2:E21	E2	=TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY($A$2:$B$21,RANDARRAY(20)),SEQUENCE(10),{1,2}),2,-1),0,1))
F2,F4:F21	F2	=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),0))),a,TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)),a)
F3	F3	=LET(Ex,FILTERXML("<k><m>"&SUBSTITUTE(C3,",","</m><m>")&"</m></k>","//m"),f,FILTER($A$2:$B$21,ISNA(MATCH($A$2:$A$21,IF(COUNTA(Ex)=1,C3),0))),TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)))

 

[camspy]

Wow, that was fast, thank you Fluff!

Could you explain why there are two formulas for F2 and F3?
I noticed, that in col F there are names, that should be excluded. It's easily to spot that in the rows, with 3 exclusions.

Also, is there a way to "re-generate" the data on request? It now changes every time I touch something so it's hard to work with the results

 

[Fluff]

The formula in F2 was left over from testing, it should be

=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),0))),TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)))

is there a way to "re-generate" the data on request?

The only way would be to set calculation to manual & then recalc when you wanted.

 

[camspy]

The only way would be to set calculation to manual & then recalc when you wanted.

Got you, thanks.

The formula in F2 was left over from testing, it should be

=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),0))),TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)))

Ok. But how to avoid generating names in col F that are listed in the exlusion list in col C?

See your last sheet.
Row 13, there are exclusions in C13: Camila,Ava,Abigail

There's the result from F13:
Mila, Emily, Elizabeth, Ella, Gianna, Camila, Harper, Isabella, Ava, Emma

See the bold ones, they shouldn't be there.

 

[Fluff]

Oops, missed the false argument in the If try

=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))),TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)))

 

[camspy]

Oops, missed the false argument in the If try

=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))),TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY(f,RANDARRAY(ROWS(f))),SEQUENCE(10),{1,2}),2,-1),0,1)))

Thank you Fluff!

in the coming days I will be testing your formulas with much bigger datasets