Random generate multiple names from a list with exclusions & sorting

camspy

New Member
Joined
Jan 7, 2022
Messages
43
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Here's my table:

Screenshot_1.png


Here's also the sheet:

test.xlsx
ABCDEF
1NamesRankExcludesimplesortedsorted+excluded
2Abigail65Olivia
3Sophia25Mila
4Amelia30
5Elizabeth80Isabella,Sophia,Amelia
6Ava15Gianna,Emma
7Isabella35Luna
8Gianna60
9Harper50
10Mila100Sofia
11Olivia5
12Charlotte20
13Evelyn45Camila,Ava,Abigail
14Avery95Charlotte
15Emily90Evelyn,Mia
16Ella75
17Sofia85Avery,Emily
18Mia40Elizabeth,Ella
19Camila55
20Emma10Harper
21Luna70
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi & welcome to MrExcel.
Should cols E & F have the same names as col D, or should they be another set of random names?
 
Upvote 0
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.
 
Upvote 0
Ok, this will do cols D & E
+Fluff 1.xlsm
ABCDE
1NamesRankExcludesimplesorted
2Abigail65OliviaMila, Evelyn, Amelia, Ella, Emily, Abigail, Charlotte, Sofia, Avery, LunaMila, Sofia, Luna, Abigail, Gianna, Camila, Harper, Mia, Sophia, Charlotte
3Sophia25MilaCharlotte, Sofia, Isabella, Emma, Amelia, Harper, Sophia, Abigail, Luna, AveryMila, Avery, Emily, Ella, Camila, Harper, Evelyn, Mia, Sophia, Charlotte
4Amelia30Mia, Elizabeth, Sofia, Ella, Harper, Amelia, Isabella, Camila, Ava, GiannaMila, Avery, Abigail, Gianna, Harper, Evelyn, Isabella, Amelia, Sophia, Olivia
5Elizabeth80Isabella,Sophia,AmeliaIsabella, Ella, Mila, Ava, Sofia, Olivia, Elizabeth, Emma, Amelia, AbigailMila, Elizabeth, Abigail, Camila, Harper, Evelyn, Mia, Sophia, Charlotte, Emma
6Ava15Gianna,EmmaCamila, Mia, Emily, Mila, Harper, Elizabeth, Abigail, Sofia, Amelia, EmmaAvery, Gianna, Camila, Harper, Mia, Isabella, Amelia, Ava, Emma, Olivia
7Isabella35LunaAva, Abigail, Avery, Luna, Charlotte, Mia, Olivia, Emily, Emma, ElizabethAvery, Emily, Sofia, Luna, Gianna, Harper, Mia, Isabella, Emma, Olivia
8Gianna60Emma, Ava, Olivia, Charlotte, Luna, Gianna, Evelyn, Sophia, Camila, IsabellaEmily, Sofia, Ella, Camila, Evelyn, Mia, Isabella, Sophia, Emma, Olivia
9Harper50Emily, Ava, Elizabeth, Abigail, Avery, Sophia, Sofia, Evelyn, Camila, OliviaAvery, Emily, Elizabeth, Ella, Luna, Abigail, Gianna, Ava, Emma, Olivia
10Mila100SofiaAbigail, Isabella, Olivia, Gianna, Avery, Elizabeth, Mia, Emily, Sofia, MilaAvery, Sofia, Elizabeth, Abigail, Gianna, Harper, Evelyn, Mia, Charlotte, Ava
11Olivia5Amelia, Elizabeth, Ella, Sophia, Luna, Avery, Sofia, Olivia, Camila, CharlotteEmily, Ella, Luna, Gianna, Camila, Harper, Mia, Amelia, Charlotte, Ava
12Charlotte20Olivia, Charlotte, Sophia, Amelia, Camila, Emily, Ella, Ava, Avery, LunaAvery, Sofia, Luna, Camila, Harper, Evelyn, Mia, Isabella, Amelia, Ava
13Evelyn45Camila,Ava,AbigailEmma, Ava, Ella, Gianna, Avery, Sophia, Camila, Harper, Isabella, CharlotteMila, Avery, Emily, Elizabeth, Abigail, Gianna, Sophia, Ava, Emma, Olivia
14Avery95CharlotteAvery, Isabella, Gianna, Ella, Amelia, Emma, Mila, Charlotte, Elizabeth, SophiaMila, Avery, Emily, Abigail, Camila, Evelyn, Amelia, Sophia, Ava, Olivia
15Emily90Evelyn,MiaCamila, Emma, Ava, Avery, Olivia, Charlotte, Gianna, Luna, Harper, EllaAvery, Emily, Luna, Abigail, Gianna, Harper, Mia, Amelia, Sophia, Emma
16Ella75Ava, Sofia, Isabella, Harper, Evelyn, Emily, Sophia, Luna, Camila, EllaAvery, Sofia, Gianna, Evelyn, Mia, Isabella, Amelia, Sophia, Emma, Olivia
17Sofia85Avery,EmilySofia, Abigail, Gianna, Isabella, Sophia, Amelia, Emily, Mia, Ella, EvelynMila, Avery, Sofia, Ella, Luna, Abigail, Gianna, Evelyn, Isabella, Emma
18Mia40Elizabeth,EllaAmelia, Emma, Evelyn, Ella, Gianna, Harper, Sofia, Sophia, Elizabeth, AveryEmily, Sofia, Luna, Abigail, Camila, Harper, Amelia, Charlotte, Ava, Emma
19Camila55Isabella, Elizabeth, Mila, Camila, Ava, Luna, Charlotte, Emma, Olivia, HarperMila, Avery, Emily, Sofia, Elizabeth, Camila, Harper, Mia, Sophia, Ava
20Emma10HarperEmma, Elizabeth, Harper, Sofia, Evelyn, Avery, Mia, Isabella, Ella, LunaMila, Emily, Sofia, Luna, Camila, Evelyn, Mia, Sophia, Ava, Olivia
21Luna70Gianna, Sofia, Emma, Ella, Camila, Mia, Olivia, Isabella, Ava, CharlotteMila, Emily, Luna, Abigail, Gianna, Mia, Isabella, Sophia, Ava, Olivia
22
Main
Cell Formulas
RangeFormula
D2:D21D2=TEXTJOIN(", ",,INDEX(SORTBY($A$2:$A$21,RANDARRAY(20)),SEQUENCE(10)))
E2:E21E2=TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY($A$2:$B$21,RANDARRAY(20)),SEQUENCE(10),{1,2}),2,-1),0,1))


Am working on F
 
Upvote 0
With col F as well
+Fluff 1.xlsm
ABCDEF
1NamesRankExcludesimplesortedsorted+excluded
2Abigail65OliviaLuna, Mia, Emma, Ava, Abigail, Charlotte, Elizabeth, Emily, Olivia, EvelynEmily, Abigail, Gianna, Camila, Harper, Isabella, Amelia, Charlotte, Ava, EmmaMila, Sofia, Ella, Luna, Gianna, Evelyn, Isabella, Sophia, Ava, Emma
3Sophia25MilaEvelyn, Emily, Isabella, Luna, Olivia, Ava, Avery, Elizabeth, Mila, CamilaAvery, Emily, Sofia, Ella, Luna, Abigail, Evelyn, Isabella, Amelia, EmmaElla, Luna, Abigail, Gianna, Harper, Mia, Isabella, Sophia, Charlotte, Olivia
4Amelia30Sophia, Olivia, Avery, Mila, Emily, Gianna, Evelyn, Ava, Camila, SofiaMila, Avery, Elizabeth, Luna, Harper, Evelyn, Isabella, Amelia, Charlotte, OliviaLuna, Gianna, Camila, Harper, Evelyn, Mia, Amelia, Sophia, Charlotte, Ava
5Elizabeth80Isabella,Sophia,AmeliaOlivia, Gianna, Avery, Emily, Luna, Sophia, Mila, Abigail, Harper, SofiaAvery, Ella, Abigail, Gianna, Camila, Harper, Evelyn, Mia, Isabella, AmeliaAvery, Emily, Sofia, Elizabeth, Ella, Luna, Gianna, Camila, Harper, Evelyn
6Ava15Gianna,EmmaOlivia, Mila, Luna, Amelia, Avery, Evelyn, Camila, Ava, Gianna, MiaMila, Avery, Elizabeth, Ella, Abigail, Gianna, Mia, Isabella, Amelia, EmmaMila, Sofia, Elizabeth, Abigail, Gianna, Evelyn, Mia, Amelia, Ava, Olivia
7Isabella35LunaSofia, Evelyn, Mia, Ella, Abigail, Harper, Charlotte, Emma, Isabella, OliviaAvery, Emily, Luna, Camila, Harper, Mia, Isabella, Amelia, Ava, OliviaAvery, Sofia, Elizabeth, Gianna, Camila, Harper, Mia, Amelia, Sophia, Emma
8Gianna60Abigail, Amelia, Mia, Mila, Emily, Evelyn, Avery, Ella, Isabella, LunaMila, Emily, Elizabeth, Ella, Gianna, Camila, Harper, Amelia, Sophia, OliviaAvery, Luna, Gianna, Camila, Harper, Evelyn, Mia, Isabella, Emma, Olivia
9Harper50Camila, Ava, Charlotte, Mila, Abigail, Emma, Ella, Emily, Luna, AveryEmily, Sofia, Luna, Abigail, Gianna, Evelyn, Mia, Isabella, Amelia, EmmaMila, Emily, Elizabeth, Luna, Gianna, Harper, Amelia, Sophia, Charlotte, Ava
10Mila100SofiaGianna, Mia, Sofia, Ava, Charlotte, Harper, Sophia, Ella, Emily, ElizabethEmily, Sofia, Ella, Gianna, Harper, Evelyn, Mia, Isabella, Ava, EmmaMila, Avery, Elizabeth, Ella, Gianna, Camila, Harper, Mia, Emma, Olivia
11Olivia5Isabella, Mila, Luna, Avery, Olivia, Elizabeth, Evelyn, Sophia, Ella, AbigailMila, Emily, Elizabeth, Luna, Gianna, Camila, Amelia, Charlotte, Ava, OliviaEmily, Luna, Camila, Harper, Evelyn, Mia, Amelia, Sophia, Charlotte, Olivia
12Charlotte20Evelyn, Mila, Abigail, Charlotte, Sophia, Olivia, Ella, Luna, Amelia, HarperMila, Avery, Emily, Elizabeth, Abigail, Gianna, Amelia, Ava, Emma, OliviaMila, Emily, Elizabeth, Ella, Luna, Abigail, Gianna, Sophia, Charlotte, Olivia
13Evelyn45Camila,Ava,AbigailOlivia, Emily, Charlotte, Isabella, Camila, Ava, Amelia, Gianna, Sophia, EmmaMila, Avery, Abigail, Camila, Harper, Sophia, Charlotte, Ava, Emma, OliviaMila, Emily, Elizabeth, Ella, Gianna, Camila, Harper, Isabella, Ava, Emma
14Avery95CharlotteAmelia, Camila, Gianna, Abigail, Mila, Luna, Ella, Avery, Emily, SofiaMila, Avery, Sofia, Elizabeth, Luna, Abigail, Gianna, Camila, Amelia, AvaAvery, Emily, Sofia, Elizabeth, Ella, Gianna, Mia, Isabella, Sophia, Emma
15Emily90Evelyn,MiaSofia, Emma, Abigail, Elizabeth, Camila, Mila, Evelyn, Mia, Emily, AmeliaAvery, Emily, Luna, Abigail, Gianna, Camila, Mia, Amelia, Sophia, OliviaMila, Emily, Sofia, Luna, Gianna, Evelyn, Amelia, Sophia, Charlotte, Emma
16Ella75Gianna, Amelia, Mila, Ava, Sophia, Abigail, Camila, Ella, Emma, OliviaMila, Sofia, Ella, Abigail, Gianna, Evelyn, Mia, Isabella, Charlotte, EmmaMila, Emily, Sofia, Elizabeth, Ella, Abigail, Mia, Isabella, Amelia, Sophia
17Sofia85Avery,EmilyLuna, Abigail, Mia, Emma, Harper, Evelyn, Sophia, Sofia, Gianna, MilaMila, Emily, Sofia, Elizabeth, Ella, Gianna, Camila, Harper, Evelyn, IsabellaMila, Avery, Sofia, Luna, Gianna, Mia, Isabella, Amelia, Charlotte, Ava
18Mia40Elizabeth,EllaSofia, Olivia, Mila, Sophia, Amelia, Emily, Isabella, Elizabeth, Mia, EmmaMila, Elizabeth, Ella, Abigail, Evelyn, Isabella, Amelia, Sophia, Emma, OliviaAvery, Emily, Sofia, Ella, Camila, Evelyn, Mia, Amelia, Sophia, Charlotte
19Camila55Mia, Amelia, Sofia, Luna, Emma, Isabella, Sophia, Harper, Abigail, OliviaEmily, Elizabeth, Ella, Luna, Abigail, Gianna, Harper, Evelyn, Mia, CharlotteMila, Avery, Sofia, Abigail, Camila, Evelyn, Mia, Isabella, Amelia, Ava
20Emma10HarperOlivia, Luna, Sophia, Ava, Charlotte, Isabella, Harper, Abigail, Ella, AmeliaAvery, Emily, Elizabeth, Luna, Abigail, Gianna, Mia, Isabella, Charlotte, EmmaMila, Avery, Emily, Elizabeth, Abigail, Camila, Evelyn, Mia, Sophia, Ava
21Luna70Evelyn, Charlotte, Gianna, Olivia, Mila, Luna, Mia, Sophia, Emily, AmeliaMila, Emily, Elizabeth, Luna, Abigail, Evelyn, Mia, Sophia, Charlotte, OliviaMila, Emily, Ella, Luna, Gianna, Camila, Mia, Amelia, Sophia, Emma
22
Main
Cell Formulas
RangeFormula
D2:D21D2=TEXTJOIN(", ",,INDEX(SORTBY($A$2:$A$21,RANDARRAY(20)),SEQUENCE(10)))
E2:E21E2=TEXTJOIN(", ",,INDEX(SORT(INDEX(SORTBY($A$2:$B$21,RANDARRAY(20)),SEQUENCE(10),{1,2}),2,-1),0,1))
F2,F4:F21F2=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)
F3F3=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)))
 
Upvote 0
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 ;)
 
Upvote 0
The formula in F2 was left over from testing, it should be
Excel Formula:
=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.
 
Upvote 0
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
Excel Formula:
=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.
 
Upvote 0
Oops, missed the false argument in the If try
Excel Formula:
=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)))
 
Upvote 0
Solution
Oops, missed the false argument in the If try
Excel Formula:
=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
 
Upvote 0

Forum statistics

Threads
1,223,929
Messages
6,175,461
Members
452,645
Latest member
Tante

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top