Here's my table:
Here's also the sheet:
I need to generate 10 random names (delimited), without duplicates, in each cell of columns D, E and F, the rules are below:
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.
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:
- In column D: just the list of 10 randomly generated and delimited names, without dupes;
- 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;
- 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.