Sub test()
Dim barcodes As Variant, rnds As Variant, lRow As Long, i As Long, rnd As Long, pickNumber As Long
lRow = Cells(Rows.Count, "B").End(xlUp).Row
barcodes = Range("B1:C" & lRow)
pickNumber = lRow * 0.3
ReDim rnds(0)
With Application
For i = 1 To pickNumber
Do
rnd = .WorksheetFunction.RandBetween(1, pickNumber)
Loop While Not IsError(.Match(rnd, rnds, 0))
barcodes(i, 2) = barcodes(rnd, 1)
rnds(UBound(rnds)) = rnd
ReDim Preserve rnds(UBound(rnds) + 1)
Next
End With
Range("B1").Resize(UBound(barcodes, 1), UBound(barcodes, 2)).Value = barcodes
End Sub
Sub test()
Dim barcodes As Variant, rnds As Variant, lRow As Long, i As Long, rnd As Long, pickNumber As Long
lRow = Cells(Rows.Count, "B").End(xlUp).Row
barcodes = Range("A1:C" & lRow)
pickNumber = lRow * 0.3
ReDim rnds(0)
With Application
For i = 1 To pickNumber
Do
rnd = .WorksheetFunction.RandBetween(1, lRow)
Loop While Not IsError(.Match(rnd, rnds, 0)) Or Not barcodes(rnd, 2) = "po"
barcodes(i, 3) = barcodes(rnd, 1)
rnds(UBound(rnds)) = rnd
ReDim Preserve rnds(UBound(rnds) + 1)
Next
End With
Range("A1").Resize(UBound(barcodes, 1), UBound(barcodes, 2)).Value = barcodes
End Sub
Sub test()
Dim barcodes As Variant, rnds As Variant, lRow As Long, i As Long, rnd As Long, pickNumber As Long
lRow = Cells(Rows.Count, "B").End(xlUp).Row
barcodes = Range("A1:B" & lRow)
Redim Preserve barcodes (1 To lRow, 1 To 3)
pickNumber = lRow * 0.3
ReDim rnds(0)
With Application
For i = 1 To pickNumber
Do
rnd = .WorksheetFunction.RandBetween(1, lRow)
Loop While Not IsError(.Match(rnd, rnds, 0)) Or Not barcodes(rnd, 2) = "po"
barcodes(i, 3) = barcodes(rnd, 1)
rnds(UBound(rnds)) = rnd
ReDim Preserve rnds(UBound(rnds) + 1)
Next
End With
For i = 1 To lRow
If i <= pickNumber Then
barcodes(i, 1) = barcodes(i, 3)
barcodes(i, 2) = "po"
Else
barcodes(i, 1) = ""
barcodes(i, 2) = ""
End If
Next
Redim Preserve barcodes (1 To lRow, 1 To 2)
Range("A1").Resize(lRow, 2).Value = barcodes
End Sub
manojxls.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | barcode14 | po | barcode6 | |||
2 | barcode1 | po | barcode1 | |||
3 | barcode5 | po | barcode1 | |||
4 | barcode4 | non-po | barcode4 | |||
5 | barcode11 | po | barcode4 | |||
6 | barcode15 | non-po | barcode6 | |||
7 | barcode1 | po | barcode5 | |||
8 | barcode6 | po | ||||
9 | barcode17 | po | ||||
10 | barcode11 | po | ||||
11 | barcode11 | po | ||||
12 | barcode7 | po | ||||
13 | barcode6 | po | ||||
14 | barcode6 | po | ||||
15 | barcode3 | po | ||||
16 | barcode17 | non-po | ||||
17 | barcode4 | po | ||||
18 | barcode7 | po | ||||
19 | barcode5 | po | ||||
20 | barcode19 | non-po | ||||
21 | barcode13 | po | ||||
22 | barcode6 | po | ||||
23 | barcode1 | po | ||||
24 | barcode4 | non-po | ||||
25 | barcode8 | po | ||||
26 | barcode17 | non-po | ||||
27 | barcode4 | po | ||||
28 | barcode4 | po | ||||
29 | barcode5 | po | ||||
Sample |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D1:D7 | D1 | =LET(f,FILTER(A1:A29,B1:B29="po"),TAKE(SORTBY(f,RANDARRAY(ROWS(f))),ROUND(ROWS(f)*0.3,0))) |
Dynamic array formulas. |