I have an inventory list of about 750 items broken down into 7 categories. Essentially I would like to have excel pick 15 random items per week for us to count. The important thing is it only picks each item number one time so they don't get counted twice.
What is the best and simplest way to go about doing this? Is there a way to show which items have already been selected.
All suggestions are greatly appreciated.
Hi SlipNot,
I don't exactly know how we could control random numbers (making sure they haven't been used already) but I have uploaded a workbook that searches 15 random cells from sheet 1 and places their values on Sheet2. Have a look and let me know how it goes. Here's the code:
Please Login or Register to view this content.
If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.
---Keep on Coding in the Free World---
Hi Slipnot,
It's not clear if, once a number has been picked, if it can then be picked again on a subsequent week but, given the information on the thread, the way I think I would do this is:
Put the numbers 1 to 750 into an array
Shuffle the array into a random order
Then pick the first 15 elements of the array (week 1), the next 15 elements of the array (week 2) etc...
This ensures that the numbers aren't repeated.
Last edited by Colin Legg; 07-25-2011 at 05:46 PM.
Sorry, I don't want the same number to be picked again untill I've cycled though all of them. Thanks.
In my own humble opinion, you can either have the numbers picked randomly (which means that a number can be repeated because it's random) or, you can have a sequential search done. I don't think this can be done both ways at once.
I used Slipknot's sample workbook (thanks Slipknot). The only change that I made is I included a second column in Sheet1 (Column B, labelled CHECKED). For each Item entry, Column B was listed as NO (meanings it hadn't been checked yet).
Once that was completed I used the following code. This code essentially cycles through the sheet to see items that have not yet been checked. Once it selects an item, it adds it to an array, then changes the CHECKED status to YES. Finally, it lists the 7 items for selection in a message box. Let me know if this helps, and if any changes can be made.
Please Login or Register to view this content.
I'm curious as to where slipnot uploaded a sample workbook? That would have been handy to work with.
How would i adjust this code to make a new sheet each time instead of pasting it on to sheet2?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks