Randomly Generated List / Macro

[Guest]

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.

 

[PY & Associates]

60000/500=120 average

first get start row numbers of each categories
then generate 100 random and unique integers not greater than 100
add start row number gives what you want please

if a category has less than 100 products, either this fails or you have to
build in check to prevent it from happening please

 

[Guest]

Thanks for trying. This does not work for my problem. Regards.

 

[Richard Buttrey]

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
__________________________