# Random 12 sample with no duplicates and 1 condition

#### willow1985

##### Well-known Member
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... 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
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
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!

• krunal123

#### Rick Rothstein

##### MrExcel MVP
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``````

• willow1985

#### Peter_SSs

##### MrExcel MVP, Moderator

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
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

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
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
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
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
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

• willow1985

Replies
12
Views
782
Replies
3
Views
153
Replies
4
Views
275
Replies
2
Views
85
Replies
4
Views
201

### Forum statistics

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.

### Which adblocker are you using?    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

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