TerrorTot38
New Member
- Joined
- Feb 2, 2022
- Messages
- 21
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
Hi All,
I have been working on sorting members of a club into Random Names into Groups of 4 People. I have 2 sheets that I use for this. This is sheet 1 below for the purpose of this I have used animals instead of names.
Following Formulas used below:
Column E Copied down to E28 First Row 5
Column F Copied down to F28 First Row 5
Column G Copied down to F28 First Row 5
Then on Sheet 2 I have the following:
In Row B3:E3 is use the filter function
which is copied into each individual column. Which would be B3
Is there away to produce the same result not using Filter? Also to copy data from Random Groups into Game 1 and Game 2 without using Paste Special - Values Only.
I am also happy to not use =RAND() if there is another way to do this.
Sorry I'm not using XL2BB as my O365 doesn't support it.
Feel free to as ask any questions and will answer them as best I can.
Thanks
I have been working on sorting members of a club into Random Names into Groups of 4 People. I have 2 sheets that I use for this. This is sheet 1 below for the purpose of this I have used animals instead of names.
Following Formulas used below:
Column E Copied down to E28 First Row 5
Excel Formula:
=IF(D5=TRUE,RAND(),"Not Valid")
Excel Formula:
=IF(E5<>"Not Valid", RANK(E5,$E$5:$E$28,1), -99)
Excel Formula:
=CEILING.MATH(F5/4,1)
Column C Row 3 | Column D Row 3 | Column E Row 3 | Column F Row 3 | Column G Row 3 |
Player | Present | Random | Rank | Game |
Duck | FALSE | Not Valid | -99 | -24 |
Bird | TRUE | 0.969600152 | 16 | 4 |
Penguin | TRUE | 0.516272895 | 9 | 3 |
Dragon | FALSE | Not Valid | -99 | -24 |
Mouse | TRUE | 0.060517056 | 1 | 1 |
Pig | TRUE | 0.589193816 | 10 | 3 |
Elephant | TRUE | 0.782223488 | 12 | 3 |
Horse | TRUE | 0.225618692 | 6 | 2 |
Cow | TRUE | 0.859498081 | 15 | 4 |
Moose | FALSE | Not Valid | -99 | -24 |
Octopus | TRUE | 0.134450165 | 4 | 1 |
Fish | FALSE | Not Valid | -99 | -24 |
Squid | TRUE | 0.128703939 | 3 | 1 |
Turtle | TRUE | 0.28877907 | 7 | 2 |
Cat | TRUE | 0.852239151 | 14 | 4 |
Dog | TRUE | 0.758370693 | 11 | 3 |
Ferret | TRUE | 0.852133831 | 13 | 4 |
Dinosaur | FALSE | Not Valid | -99 | -24 |
Bear | TRUE | 0.173209906 | 5 | 2 |
Ardvark | TRUE | 0.102160204 | 2 | 1 |
Tiger | FALSE | Not Valid | -99 | -24 |
Lion | FALSE | Not Valid | -99 | -24 |
Bee | FALSE | Not Valid | -99 | -24 |
Owl | TRUE | 0.40193213 | 8 | 2[/CODE] |
Then on Sheet 2 I have the following:
In Row B3:E3 is use the filter function
Excel Formula:
=FILTER(Players!$C$5:$C$28, Players!$G$5:$G$28=B2,"")
Column B | Column C | Column D | Column E | |
1 | 2 | 3 | 4 | |
RANDOM Groups Row 3 | Horse | Bird | Penguin | Mouse |
Cow | Elephant | Pig | Octopus | |
Dog | Ferret | Turtle | Squid | |
Ardvark | Owl | Bear | Cat | |
Game 1 (Row 10) | Court 1 | Court 2 | Court 3 | Court 4 |
Pig | Bird | Horse | Elephant | |
Cat | Penguin | Dog | Cow | |
Ferret | Mouse | Bear | Octopus | |
Owl | Turtle | Ardvark | Squid | |
Game 2 (Row 16) | Court 1 | Court 2 | Court 3 | Court 4 |
Cow | Penguin | Bird | Pig | |
Squid | Mouse | Elephant | Octopus | |
Turtle | Horse | Bear | Ferret | |
Dog | Cat | Owl | Ardvark |
Is there away to produce the same result not using Filter? Also to copy data from Random Groups into Game 1 and Game 2 without using Paste Special - Values Only.
I am also happy to not use =RAND() if there is another way to do this.
Sorry I'm not using XL2BB as my O365 doesn't support it.
Feel free to as ask any questions and will answer them as best I can.
Thanks