I'm in the process of using Google Sheets to make a Secret Santa Generator, but have come across a problem that I can't seem to get around. How do you ensure that people aren't given a giftee that is part of the same family group?
Currently I have a working system that looks at the first names of people and checks to see whether someone has been allocated themselves. But can this be done by taking into account first and last names to ensure that someone from the same family isn't given their partner?
Currently my formulas are as below;
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Person | Rand No. | Rank | Giftee | Run Again? | |
| 2 | 1 | Louise H. | =RAND() | =RANK(C2,C2:C5) | =VLOOKUP(D2,A2:B5,2,0) | =IF(B2=E2,"Error - Run Again","") |
| 3 | 2 | Matt H. | =RAND() | =RANK(C3,C3:C5) | =VLOOKUP(D3,A2:B5,2,0) | =IF(B3=E3,"Error - Run Again","") |
| 4 | 3 | Matt C. | =RAND() | =RANK(C4,C3:C5) | =VLOOKUP(D4,A2:B5,2,0) | =IF(B4=E4,"Error - Run Again","") |
| 5 | 4 | Liz C. | =RAND() | =RANK(C5,C3:C5) | =VLOOKUP(D5,A2:B5,2,0) | =IF(B5=E5,"Error - Run Again","") |
| 6 | 5 | Barbara D. | =RAND() | =RANK(C6,C3:C5) | =VLOOKUP(D6,A2:B5,2,0) | =IF(B6=E6,"Error - Run Again","") |
| 7 | 6 | Barbara D. | =RAND() | =RANK(C7,C3:C5) | =VLOOKUP(D7,A2:B5,2,0) | =IF(B7=E7,"Error - Run Again","") |
And so on and so on for as many other people as required.
Anyone have some ideas to take the family situation into account?
CodePudding user response：
If your list has always last name in the second name you could set this formula:
=byrow(B2:B,lambda(each,if(each="","",if(REGEXEXTRACT(each,"\s [^\s] ")=REGEXEXTRACT(offset(each,0,3),"\s [^\s] "),"Same family - Run again",""))))
NOTE: the value 3 of offset determines the amount of columns that separates those two names. If it changes, change that number
One thing you could do in order not to get your giftee solutions re-arranged further than you'd like you could make a copy of B column and randomise:
This way you can randomise only when you want (and you could spare C and D columns)
CodePudding user response：
use in row 2 and drag down:
=INDEX(LAMBDA(x, SINGLE(QUERY(SORT({x, REGEXEXTRACT(x, "\b\w \b$")},
RANDARRAY(ROWS(x)), ), "select Col1 where not Col2 ends with '"&
REGEXEXTRACT(A2, "\b\w \b$")&"'"&IF(ROW()=2,," and not Col1 matches '"&
TEXTJOIN("|", 1, C1:C$2)&"'"), )))(A$2:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))
CodePudding user response：
Using your current setup try the following modification:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Person | Rand No. | Rank | Giftee | Run Again? | |
| 2 | 1 | Louise H. | =RAND() | =RANK(C2,C$2:C$7) | =VLOOKUP(D2, $A$2:$B$7, 2, 0) | =IF(B2=E2,"Same Person- Run Again",IF(REGEXEXTRACT(B2," (.)")=REGEXEXTRACT(E2," (.)"),"Same Family - Run Again","")) |
| 3 | 2 | Matt H. | =RAND() | =RANK(C3,C$2:C$7) | =VLOOKUP(D3, $A$2:$B$7, 2, 0) | =IF(B3=E3,"Same Person- Run Again",IF(REGEXEXTRACT(B3," (.)")=REGEXEXTRACT(E3," (.)"),"Same Family - Run Again","")) |
| 4 | 3 | Matt C. | =RAND() | =RANK(C4,C$2:C$7) | =VLOOKUP(D4, $A$2:$B$7, 2, 0) | ==IF(B4=E4,"Same Person- Run Again",IF(REGEXEXTRACT(B4," (.)")=REGEXEXTRACT(E4," (.)"),"Same Family - Run Again","")) |
| 5 | 4 | Liz C. | =RAND() | =RANK(C5,C$2:C$7) | =VLOOKUP(D5, $A$2:$B$7, 2, 0) | =IF(B5=E5,"Same Person- Run Again",IF(REGEXEXTRACT(B5," (.)")=REGEXEXTRACT(E5," (.)"),"Same Family - Run Again","")) |
| 6 | 5 | Barbara D. | =RAND() | =RANK(C6,C$2:C$7) | =VLOOKUP(D6, $A$2:$B$7, 2, 0) | =IF(B6=E6,"Same Person- Run Again",IF(REGEXEXTRACT(B6," (.)")=REGEXEXTRACT(E6," (.)"),"Same Family - Run Again","")) |
| 7 | 6 | Barbara D. | =RAND() | =RANK(C7,C$2:C$7) | =VLOOKUP(D7, $A$2:$B$7, 2, 0) | =IF(B7=E7,"Same Person- Run Again",IF(REGEXEXTRACT(B7," (.)")=REGEXEXTRACT(E7," (.)"),"Same Family - Run Again","")) |
Result:
Explanation:
For the column F this compares the column B and E if they are the same person. This also extracts the last name (character after space) and compares them if they are from the same family.