I have a list of 60000 products. These products are grouped into 500
categories (for example ABC, EFG ,HIJ).
I would like to randomly generate a list of 100 products given a category.
For example, for category ABC, I would like the macro to generate a list of
100 products.
Is this possible ?
Thank you in advance.
One way
With the name "Products" in A1, "Cat" in B1, Product numbers in
A2:A60001, and categories in B2:B60001
Put your chosen category in C2 and run the folloiwng macro.
This will first extract all the products for your chosen category in
columns E & F, then enter a random number in column G2:Gxx, then sort
columns E:G, and finally number column G starting at 1 and
incrementing by 1
You will then have all your products for the chosen category listed in
random order. If you want 100, just pick off numbers 1-100 in column G
Sub RandProducts()
Range("a1:B60001").AdvancedFilter Action:=xlFilterCopy,
Criteriarange:=Range("C1:c2"), copyToRange:=Range( _
"E1:F1"), Unique:=False
Range(Range("F2"), Range("F2").End(xlDown)).Offset(0, 1) =
"=Rand()"
Range(Range("G2"), Range("G2").End(xlDown)).Copy
Range("G2").PasteSpecial (xlPasteValues)
Range(Range("E2"), Range("G2").End(xlDown)).Sort key1:=Range("G2")
Range("G2") = 1: Range(Range("G2"),
Range("G2").End(xlDown)).DataSeries step:=1
End Sub
Watch any word wrap above. Adjust for your data range
HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________