Random number 1 - x

[CaptBrownShoes]

Here is the situation:
The table above is needing a random number that does not duplicate based on the remaining cards in deck.
The correct answer would work as follows:

• RAND column looks at Discard Pile and if = "a" (webdings check), then "". Done!
• Then the RAND column would look at the number of remaining cards "12" in Odds Total column as the max random number. Done!
• Then the RAND column picks a number from 1 - 12 that does not exist in the previous attempts. NOPE!!! Can't figure this one out.

=IF([@[Discard Pile a]]="a","",RANDBETWEEN(1,tblExCards[[#Totals],[Odds]]))

I cannot get that to happen. Any suggestions?

Thus, is the a need for a VBA statement?

Thank you Team!

 

[Cubist]

Try:

Book1
	A	B	C
1	Odds	Discard Pile	Rand
2	8.30%		9
3		✓
4	8.30%		7
5		✓
6	8.30%		10
7	8.30%		8
8		✓
9	8.30%		12
10		✓
11	8.30%		11
12		✓
13	8.30%		2
14		✓
15	8.30%		4
16	8.30%		6
17		✓
18	8.30%		5
19		✓
20	16.70%		3
21	16.70%		1
22	12	8
Sheet4

Cell Formulas
Range	Formula
C2:C21	C2	=LET( m,MAP(B2:B21,LAMBDA(m,COUNTIFS(INDEX(B2:B21,1):m,""))), IF(B2:B21="",INDEX(SORTBY(SEQUENCE(A22),RANDARRAY(A22)),IF(B2:B21="",m)),""))
A22	A22	=COUNTBLANK(B2:B21)
Dynamic array formulas.

 

[Cubist]

A little shorter.

=IF(B2:B21="",INDEX(SORTBY(SEQUENCE(A22),RANDARRAY(A22)),MAP(B2:B21,LAMBDA(m,COUNTIFS(INDEX(B2:B21,1):m,"")))),"")

 

[CaptBrownShoes]

It wants to work, but I get a #NAME? error. I don't think LAMBDA is known.

 

[Cubist]

It is in 365 (based on your profile).

LAMBDA function - Microsoft Support

support.microsoft.com

 

[CaptBrownShoes]

It is in 365 (based on your profile).

LAMBDA function - Microsoft Support

support.microsoft.com

I am new to this forum and selected the wrong one. Just updated.

 

[Phuoc]

Or try this:

Book1
	A	B	C
1	Odds	Discard Pile	Rand
2	0.083		1
3		✓
4	0.083		9
5		✓
6	0.083		2
7	0.083		10
8		✓
9	0.083		7
10		✓
11	0.083		11
12		✓
13	0.083		3
14		✓
15	0.083		12
16	0.083		4
17		✓
18	0.083		8
19		✓
20	0.167		5
21	0.167		6
22	12
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=IF(B2="",AGGREGATE(15,6,SEQUENCE($A$22)/(COUNTIFS($C$1:C1,SEQUENCE($A$22))=0),RANDBETWEEN(1,$A$22-COUNT($C$1:C1))),"")
A22	A22	=COUNT(A2:A21)

 

[CaptBrownShoes]

Or try this:

Book1
	A	B	C
1	Odds	Discard Pile	Rand
2	0.083		1
3		✓
4	0.083		9
5		✓
6	0.083		2
7	0.083		10
8		✓
9	0.083		7
10		✓
11	0.083		11
12		✓
13	0.083		3
14		✓
15	0.083		12
16	0.083		4
17		✓
18	0.083		8
19		✓
20	0.167		5
21	0.167		6
22	12
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=IF(B2="",AGGREGATE(15,6,SEQUENCE($A$22)/(COUNTIFS($C$1:C1,SEQUENCE($A$22))=0),RANDBETWEEN(1,$A$22-COUNT($C$1:C1))),"")
A22	A22	=COUNT(A2:A21)

Thank you!!!! Worked SWIMMINGLY!!!