On Sat, 8 Apr 2006 05:08:02 -0700, carl
wrote:

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
__________________________