select random 30% of given row

[manojxls]

Hi,
please help me select column b ,random 30% of given "po" in column.
data is large, so please help me a formula.

	a	b	c
1	barcode1	po
2	barcode2	po
3	barcode5	po
4	barcode3	non-po
5	barcode3	po
6	barcode7	po

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help clarify if you gave a slightly larger sample (say 20-30 rows) and sample results, preferably with XL2BB, so that we can see just what sort of results layout you want.

 

[Legacy 143009]

It is quite difficult to generate unique randoms via formula. Please try the code below:

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

 

[Legacy 143009]

I am sorry, I both got your question wrong and my code had a flaw. Please find the updated code:

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

 

[manojxls]

hi,
let say forgot "c" column
suppose from 500 row say column "a" i want to select or filter only 30% from given 500 row.
like if "a" column row is 10 it will select only 3 as 30%.

 

[Legacy 143009]

Does "po" rule still apply?

 

[manojxls]

Does "po" rule still apply?

in column b i have "po" and "non po" i will filter po
than selected po will want to select 30% of column "a"

 

[Legacy 143009]

This must do the job

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

 

[Peter_SSs]

Would a formula approach like be any good?

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.