Coaches Random Numbers

[GomaPile]

Hello all,

Can someone help please. We have a school soccer team where we draw numbers out of a bag, who goes first lower to highest or odds & even etc... Found this formula that works well for our school soccer team.

What Im trying to achieve using my random personal numbers, after pressing F9 multiple times some of Cells have repeated with the same value more than once, where I would prefer 3 random unique numbers everytime.

The formula below is copied 3 times with the same info across 3 separate cells from A1), B1), C1)

=INDEX(K1:K10, RANDBETWEEN(1, COUNT(K1:K10)), 1)

My list of personal Coaches Random Numbers in Cells K1:K10 downwards, and no 2 numbers are displayed with same value, every number is unique.

We appreciate anyone's support

Kind Regards
Nasa2

 

[Phuoc]

Try this:

Book1
	A	B	C								K
1	K01	K06	K07								K01
2											K02
3											K03
4											K04
5											K05
6											K06
7											K07
8											K08
9											K09
10											K10
Sheet1

Cell Formulas
Range	Formula
A1:C1	A1	=LET(a,RANDARRAY(ROWS(K1:K10)),TRANSPOSE(FILTER($K$1:$K$10,a>=LARGE(a,3))))
Dynamic array formulas.

 

[GomaPile]

Hey [COLOR=rgb(110, 146, 115)]Phuoc[/COLOR],

A quick thank you with your response & support. We added your formula below but it didn't work, it returned #NAME? error in cell A1.

​

=LET(a,RANDARRAY(ROWS(K1:K10)),TRANSPOSE(FILTER($K$1:$K$10,a>=LARGE(a,3))))​

​

 Nasa2

 

[Phuoc]

Are you using O365 or another version?

Book1
	A	B	C								K
1	K03	K08	K10								K01
2											K02
3											K03
4											K04
5											K05
6											K06
7											K07
8											K08
9											K09
10											K10
Sheet1

Cell Formulas
Range	Formula
A1	A1	=INDEX($K$1:$K$10, RANDBETWEEN(1, ROWS($K$1:$K$10)))
B1:C1	B1	=INDEX($K$1:$K$10,LARGE(ROW($K$1:$K$10)*(COUNTIF($A$1:A1,$K$1:$K$10)=0),RANDBETWEEN(1, ROWS($K$1:$K$10)-ROWS($A$1:A1))))

 

[GomaPile]

Hey mate (@Phuoc),

Thanks again. Yes I'm using Office 365 sorry should have mentioned that earlier.

We're half way there!! What I mean by that statement the first two formulas works well. I copied the second code in both cells B1 & C1 however it repeated the same value multiple times in B1 & C1, but NOT in A1

One result came back with.
A1) 3
B1) 8
C1) 8

And I double and triple checked every F9 to make sure.

Nasa2

 

[felixstraube]

Maybe something like this?

Libro1.xlsx
	A	B
1	Random number from 1 to:	20
2	Size of result array	3
3
4	Result array	11
5		15
6		16
Hoja2

Cell Formulas
Range	Formula
B4:B6	B4	=LET(n, B1, rand,HSTACK(RANDARRAY(n),SEQUENCE(n)), TAKE(DROP(SORT(rand,1),,1),B2))
Dynamic array formulas.

 

[felixstraube]

Or maybe this? I don't know if I understood correctly
(in K1:K10 you can enter the coach numbers you want)

Libro1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	61	5	72								98
2											82
3											2
4											58
5											5
6											72
7											3
8											61
9											8
10											63
Hoja2

Cell Formulas
Range	Formula
A1:C1	A1	=LET(cn, $K$1:$K$10, rand,HSTACK(RANDARRAY(ROWS(cn)),cn), TRANSPOSE(TAKE(DROP(SORT(rand,1),,1),3)))
Dynamic array formulas.

 

[GomaPile]

Hey [COLOR=rgb(110, 146, 115)]felixstraube[/COLOR]

Thank you mate. We're using Office 365 here and I don't think your code will work.

Couple of early posts above Phuoc has already trieit that and it returned a #NAME? error.


Nasa2

 

[Eric W]

If you're getting a #NAME? error, that likely means that your version of Excel doesn't recognize one of the functions in that formula. Are you sure that you're using Excel 365? If so, do you know when it was updated last?

Here are a couple versions using older functions:

Book1
	A	B	C	D	E	F
1		Results				List
2	365	1	3	7		1
3						2
4	2013	10	7	5		3
5						4
6						5
7						6
8						7
9						8
10						9
11						10
12
Sheet3

Cell Formulas
Range	Formula
B2:D2	B2	=INDEX(SORTBY(F2:F11,RANDARRAY(10)),{1,2,3})
B4:D4	B4	=AGGREGATE(14,6,$F$2:$F$11/(COUNTIF($A4:A4,$F$2:$F$11)=0),RANDBETWEEN(1,COUNT($F$2:$F$11)-COLUMNS($B:B)+1))
Dynamic array formulas.

The B2 formula uses functions available in Excel 2021.

THe B4 formula uses functions available in Excel 2013. SInce that version doesn't SPILL results, you have to drag the formulas. Also, it's required to have a cell to the left (A4 here) which does not have any of the F2:F11 values in it. Also, the F2:F11 values must be numbers. If you want text, that's possible too, but I'll have to adjust the formula.

 

[GomaPile]

Hey mate,

Yes definitely using Office 365 here.

And WE tried again using your first post formula on other Office pc's and all came back with #NAME? error.

However your 2nd post formula actually worked brilliantly, thank-you so much!!

Also, you mentioned it's possible to add text too with numbers... that's awesome. You read my mind, cos we were discussing that yesterday thinking having a mystery prize for our little soccer league.

Let me discuss this further with my team whether we need this option. I'll get back to you soon.

Anyway we're sooo happy that the formula returns with different numbers it's make our job easier now.

Thanks
Nasa2