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

Cheeks1969

New Member
Joined
Dec 7, 2016
Messages
5
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,661
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.
 
Last edited:

Cheeks1969

New Member
Joined
Dec 7, 2016
Messages
5
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

Well-known Member
Joined
Sep 21, 2016
Messages
2,661
With the pre-selected cells in A1:A4, the following macro will put the 6 random numbers in B1:B6 :
Code:
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
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.
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top