Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly Generated List / Macro
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly Generated List / Macro
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 "carl" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly Generated List / Macro
Thanks for trying. This does not work for my problem. Regards.
"PY & Associates" wrote: 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 "carl" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Randomly Generated List / Macro
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I randomly shuffle a word list in Excel? | Excel Discussion (Misc queries) | |||
Finding and compiling list of cells containing data... | Excel Worksheet Functions | |||
List Box with macro | Excel Worksheet Functions | |||
running a macro from a list | Excel Worksheet Functions | |||
How to randomly select from a list with condition | Excel Worksheet Functions |