Random 12 sample with no duplicates and 1 condition

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
Hello

I need help with acquiring a random 12 numbers from Column A in the blue table (right). No numbers can be duplicated on the list (column A on the left) and I need the formula to only select Work Order numbers that have an "N" in column E in the table (right)

(see below picture)

I hope I am explaining this well...

1603833016820.png


Thank you to anyone who can help :)

Carla
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
Are those examples on two different sheets? If so, what are their names?

What is the table's name?

Is a VBA solution acceptable (I'm not sure how to do it with formulas)?
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
Are those examples on two different sheets? If so, what are their names?

What is the table's name?

Is a VBA solution acceptable (I'm not sure how to do it with formulas)?
Yes they are 2 sheets. You can call them sheet 1 and sheet 2 and table. I will rename as needed. If you don't know a solution by formulas VBA is also welcome. Thank you very much!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
Give this macro a try...
VBA Code:
Sub Carla()
  Dim R As Long, Cnt As Long, RandomIndex As Long, Arr As Variant, Tmp As Variant
  Const HowMany = 12
  Randomize
  Arr = Sheets("Sheet1").ListObjects("Table").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Arr)
      If Arr(R, 5) = "N" Then .Item(CStr(Arr(R, 1))) = 1
    Next
    Arr = .Keys
  End With
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Sheets("Sheet2").Range("A3").Resize(HowMany) = Application.Transpose(Arr)
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It really would help if in future you could post sample data with XL2BB as you have done before. It saves helpers a lot of typing to set up to test. ;)

I need the formula ...
Since you don't want repeats in the results, I have assumed no repeats in the table also.

willow1985.xlsm
AE
1W/O NumberPreviously Audited
21Y
32Y
43N
54N
65Y
76Y
87N
98N
109Y
1110N
1211N
1312N
1413N
1514N
1615N
1716N
1817N
1918Y
2019N
Sheet2


You will need to adjust sheet, table & table column names to match your set-up.

willow1985.xlsm
A
1
2W/O Number
315
412
517
614
711
810
98
103
114
127
1316
1413
15
Sheet1
Cell Formulas
RangeFormula
A3:A14A3=AGGREGATE(15,6,IF(Table1[Previously Audited]="N",Table1[W/O Number],NA())/ISNA(MATCH(IF(Table1[Previously Audited]="N",Table1[W/O Number]),A$2:A2,0)),RANDBETWEEN(1,COUNTIF(Table1[Previously Audited],"N")-COUNT(A$2:A2)))



FWIW, this was my approach for a vba solution:

VBA Code:
Sub Random_Values()
  Dim a As Variant
  Dim AL As Object
  Dim i As Long
  
  Randomize
  With Sheets("Sheet2").ListObjects("Table1").DataBodyRange
    a = Application.Index(.Cells.Value, Evaluate("row(1:" & .Rows.Count & ")"), Array(1, 5))
  End With
  Set AL = CreateObject("System.Collections.ArrayList")
  For i = 1 To UBound(a)
    If a(i, 2) = "N" Then AL.Add Rnd() & "#|" & a(i, 1)
  Next i
  AL.Sort
  a = Filter(Split(Join(AL.ToArray, "|"), "|"), "#", False)
  ReDim Preserve a(1 To 12)
  Sheets("Sheet1").Range("A3:A14").Value = Application.Transpose(a)
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
a = Filter(Split(Join(AL.ToArray, "|"), "|"), "#", False)
ReDim Preserve a(1 To 12)
Sheets("Sheet1").Range("A3:A14").Value = Application.Transpose(a
You can replace the above three lines of code from you macro with this single line of code and it will work the same...
VBA Code:
Sheets("Sheet2").Range("A3:A14").Value = Application.Transpose(Filter(Split(Join(AL.ToArray, "|"), "|"), "#", False))
Here the receiving range limits the input to the first 12 items in the array so there is no need to ReDim Preserve to accomplish this same outcome.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Give this macro a try...
VBA Code:
Sub Carla()
  Dim R As Long, Cnt As Long, RandomIndex As Long, Arr As Variant, Tmp As Variant
  Const HowMany = 12
  Randomize
  Arr = Sheets("Sheet1").ListObjects("Table").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Arr)
      If Arr(R, 5) = "N" Then .Item(CStr(Arr(R, 1))) = 1
    Next
    Arr = .Keys
  End With
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Sheets("Sheet2").Range("A3").Resize(HowMany) = Application.Transpose(Arr)
End Sub
This code is perfect.

A couple more questions though so I can understand the code better:

if I wanted to make it more basic and only select a random 12 from the list without worrying about the "N" Flag in column E what would I remove/modify of this code?
Also can I make the 12 sample size a cell reference instead where the user can control how many is selected?

Thank you very much!!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
To select random names without regard to the Y or Y flag, simply remove this part of the line of code inside the first For-Next loop and leave the rest as is...

If a(i, 2) = "N" Then

To make the code look at a cell for the number of random values to display, remove the entire Const statement and add this to the Dim statement...

HowMany As Long

then, in the code, assign the cell's value property to the HowMany variable.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
To select random names without regard to the Y or Y flag, simply remove this part of the line of code inside the first For-Next loop and leave the rest as is...

If a(i, 2) = "N" Then

To make the code look at a cell for the number of random values to display, remove the entire Const statement and add this to the Dim statement...

HowMany As Long

then, in the code, assign the cell's value property to the HowMany variable.

I am getting an error: Type mismatch with the last line before End Sub

Here is my new code:

VBA Code:
Sub RS()
'
' RS Macro
'
  
      If Application.CountIf(Sheets("Random Sample").Range("E1"), "") > 0 Then
        MsgBox "Please Enter User Name"
        Exit Sub
    End If


  
  Dim R As Long, Cnt As Long, RandomIndex As Long, Arr As Variant, Tmp As Variant, HowMany As Long
  HowMany = Sheets("Random Sample").Range("C1").Value
  Randomize
  Arr = Sheets("DATA").ListObjects("DATA").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    
    Arr = .Keys
  End With
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Sheets("Random Sample").Range("B3").Resize(HowMany) = Application.Transpose(Arr)
  

End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
You took out too much... you removed the entire loop where as I only wanted you to remove the part I showed you from the single code line inside the loop. Here is the entire macro with the changes (including your cell reference) in place...
VBA Code:
Sub Carla()
  Dim R As Long, Cnt As Long, RandomIndex As Long, HowMany As Long, Arr As Variant, Tmp As Variant
  HowMany = Sheets("Random Sample").Range("C1").Value
  Randomize
  Arr = Sheets("Sheet1").ListObjects("Table").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Arr)
      .Item(CStr(Arr(R, 1))) = 1
    Next
    Arr = .Keys
  End With
  For Cnt = UBound(Arr) To LBound(Arr) Step -1
    RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  Sheets("Sheet2").Range("A3").Resize(HowMany) = Application.Transpose(Arr)
End Sub
NOTE: I did not include the test you added at the beginning of my macro. I would point out that it seems overly complicated for what I think it is doing. It looks like you should be able to test the cell for being empty and accomplish the same thing I think you are trying to do. If I am right, this would be my suggestion for the If..Then test...

If Sheets("Random Sample").Range("E1").Value = "" Then
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,248
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top