choose random numbers from list excluding some, or automatically refresh formula until results ok

[Cheeks1969]

Hi,
I have a list of 20 values in excel. I want to pick four by a certain rule, which I have figured out. Now I want to pick 6 randomly from the remaining 16.
I used 'randbetween' for the whole range of values (since I won't know ahead of time which 4 will be picked by the rules) along with a "duplicate?" function that says 'True' if any of the randomly chosen values match another randomly chosen value or one of the four chosen by the rules.

I'm curious if someone knows how to automatically refresh (f9- generate new random numbers) until they are all unique values - 'False' on the duplicate checker. Is there a way to do this with formulas only (no macros or arrays) ???

Alternatively, is there a way to choose randomly from a list while excluding certain cells that are previously chosen, but not known ahead of time, or what place they will be in the list?

Thanks in advance for any replies!

 

[footoo]

This would be better with VBA.

Heres a way without VBA, but it's a bit klunky.
Set-up :
• Your pre-selected 4 numbers in A1:A4
• Numbers 1 to 20 in B1:B20 (they do not need to be in sequence)
• Put in C1 and fill down =IF(ISNA(VLOOKUP(B1,A$1:A$4,1,0),B1,"")
• Put in D1 and fill down =RAND()

Then select columns B:D and sort by column D.
The first 6 numbers displayed in column C will be the random numbers.

 

[Cheeks1969]

Thanks, that makes sense. And then I could recompile the list by starting with the four and referring back to the associated values next to the random numbers using vlookup.

Had to change it slightly to ".... 0,)),B1...." added a ).

This would be better with VBA.

Heres a way without VBA, but it's a bit klunky.
Set-up :
• Your pre-selected 4 numbers in A1:A4
• Numbers 1 to 20 in B1:B20 (they do not need to be in sequence)
• Put in C1 and fill down =IF(ISNA(VLOOKUP(B1,A$1:A$4,1,0),B1,"")
• Put in D1 and fill down =RAND()

Then select columns B:D and sort by column D.
The first 6 numbers displayed in column C will be the random numbers.

 

[footoo]

With the pre-selected cells in A1:A4, the following macro will put the 6 random numbers in B1:B6 :

Sub RandomNumbers()
Dim i%, coll As New Collection, n%
For i = 1 To 20
    If [A1:A4].Find(i, LookAt:=xlWhole) Is Nothing Then coll.Add i
Next
Randomize
For i = 1 To 6
    n = Int(coll.Count * Rnd + 1)
    Cells(i, "B") = coll(n)
    coll.Remove n
Next
'If you want the numbers in sequence :
[B1:B6].Sort Key1:=[B1], Order1:=xlAscending, Header:=xlNo
End Sub

 

[Marcelo Branco]

A possible solution using formulas


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Pre-Selected​

[/td][td]

Numbers​

[/td][td][/td][td]

Six random​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

4​

[/td][td]

1​

[/td][td][/td][td]

5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

8​

[/td][td]

2​

[/td][td][/td][td]

14​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

12​

[/td][td]

3​

[/td][td][/td][td]

18​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

19​

[/td][td]

4​

[/td][td][/td][td]

13​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td]

5​

[/td][td][/td][td]

10​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td]

6​

[/td][td][/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td]

7​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td]

8​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td][/td][td]

9​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td][/td][td]

10​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td][/td][td]

11​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td]

12​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td][/td][td]

13​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td]

14​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

16​

[/td][td][/td][td]

15​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

17​

[/td][td][/td][td]

16​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

18​

[/td][td][/td][td]

17​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

19​

[/td][td][/td][td]

18​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

20​

[/td][td][/td][td]

19​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

21​

[/td][td][/td][td]

20​

[/td][td][/td][td][/td][/tr]
[/table]

Array formula in D2 copied down until D7
=INDEX(B$2:B$21,SMALL(IF(ISNA(MATCH(B$2:B$21,A$2:A$5,0))*ISNA(MATCH(B$2:B$21,D$1:D1,0)),ROW(B$2:B$21)-ROW(B$2)+1,0),RANDBETWEEN(4+ROWS(D$2:D2),20)))
Ctrl+Shift+Enter, not just Enter

M.

 

[Mats80]

A possible solution using formulas

	A​	B​	C​	D​
1​	Pre-Selected​	Numbers​		Six random​
2​	4​	1​		5​
3​	8​	2​		14​
4​	12​	3​		18​
5​	19​	4​		13​
6​		5​		10​
7​		6​		1​
8​		7​
9​		8​
10​		9​
11​		10​
12​		11​
13​		12​
14​		13​
15​		14​
16​		15​
17​		16​
18​		17​
19​		18​
20​		19​
21​		20​

Array formula in D2 copied down until D7
=INDEX(B$2:B$21,SMALL(IF(ISNA(MATCH(B$2:B$21,A$2:A$5,0))*ISNA(MATCH(B$2:B$21,D$1:D1,0)),ROW(B$2:B$21)-ROW(B$2)+1,0),RANDBETWEEN(4+ROWS(D$2:D2),20)))
Ctrl+Shift+Enter, not just Enter

M.

Hi Marcelo,
With the new Dynamic Arrays in Excel, is there an update to the above formula? Thanks in Advance.
Mats.

 

[Mats80]

This would be better with VBA.

Heres a way without VBA, but it's a bit klunky.
Set-up :
• Your pre-selected 4 numbers in A1:A4
• Numbers 1 to 20 in B1:B20 (they do not need to be in sequence)
• Put in C1 and fill down =IF(ISNA(VLOOKUP(B1,A$1:A$4,1,0),B1,"")
• Put in D1 and fill down =RAND()

Then select columns B:D and sort by column D.
The first 6 numbers displayed in column C will be the random numbers.

Hi Footoo,
With the new Dynamic Arrays in Excel, is there an update to the above formula? Thanks in Advance.
Mats.

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E
1	Pre-Selected	List		# of random from remainder	Random list
2	3	1		6	18
3	5	2			9
4	7	3			8
5	11	4			20
6		5			10
7		6			12
8		7
9		8
10		9
11		10
12		11
13		12
14		13
15		14
16		15
17		16
18		17
19		18
20		19
21		20
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=LET(a,FILTER(B2:B21,COUNTIF(A2:A10,B2:B21)=0),INDEX(SORTBY(a,RANDARRAY(ROWS(a))),SEQUENCE(D2)))
Dynamic array formulas.

 

[Mats80]

Maybe:

Book1
	A	B	C	D	E
1	Pre-Selected	List		# of random from remainder	Random list
2	3	1		6	18
3	5	2			9
4	7	3			8
5	11	4			20
6		5			10
7		6			12
8		7
9		8
10		9
11		10
12		11
13		12
14		13
15		14
16		15
17		16
18		17
19		18
20		19
21		20
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=LET(a,FILTER(B2:B21,COUNTIF(A2:A10,B2:B21)=0),INDEX(SORTBY(a,RANDARRAY(ROWS(a))),SEQUENCE(D2)))
Dynamic array formulas.

This is Brilliant. Thanks heaps Eric.
Curiously can we wrap a sort/sortby around the sequence values within the formula in E2.

 

[Eric W]

Sure:

=LET(a,FILTER(B2:B21,COUNTIF(A2:A10,B2:B21)=0),SORT(INDEX(SORTBY(a,RANDARRAY(ROWS(a))),SEQUENCE(D2))))