VBA for random selection of numbers from a column with several conditions

[dchapman17]

Good morning.

Moderator edit: Refer to: This thread

Apologies for hijacking a thread, however the original posters requirement is very similar to mine.

I have taken the VB code and tried to manipulate it to provide the information that I require, however I've not had any success. I can get the Macro to run for the code posted in #11 but when I try to reduce the number of searched rows and change the number of A, B and C results provided then I encounter a problem.

I have 538 rows of data consisting of 205 category A's (rows 2 to 206), 125 category B's (rows 207 to 331) and 208 category C's (rows 332 to 539).

I would like to randomly select 10 x A, 3 x B and 1 x C on a daily basis when I manually run the Macro. If at all possible, the A's need to reappear every 21 days, the B's need to appear every 42 days and the C's once every 253 days.

I would appreciate any help that can be offered. Thank you in advance.

 

[Peter_SSs]

Apologies for hijacking a thread, however the original posters requirement is very similar to mine.

So best to start your own thread and include a link back to the old one. In this case I have done that for you.

I'm a little unsure of your layout and exact requirement. Any chance you could make up a small set of dummy data and post that, a set of sample expected results with XL2BB and explain again in relation to that sample?

 

[Marc L]

Hi,​

​

expecting also an attachment at least, as using Excel as a database software is not such a great idea so a question : can 'A B C' be sorted ?​

A column must be used as a date stamp …​

 

[dchapman17]

Thank you Peter.

I have attached a screen shot of sample data. Unfortuantely I am unable to download XL2BB as I am sending this from a company computer and it's against the IT policy to download and install software, Add-Ins etc.

My data is covered in Columns A, B and C over 538 rows (2 to 539) plus the heading row (1);
Column A - Part No
Column B - Part Description
Column C - Category (A, B or C)

The data is sorted by the Category (A - rows 2 to 206, B - rows 207 to 331 and C - rows 332 to 539) and the data in Column A is then sorted sequentialy for each Category, i.e. 1, 2, 3 etc for Category A, 207, 208, 209 etc for Category B, 332, 333, 334 etc for Category C.

I would like to randomly select 10 x Category A, 3 x Category B and 1 x Category C when I manually run the Macro on a daily basis. As each Category has a different priod in which the part needs to be counted, I would like:
Category A - to not appear again within a 21 day period.
Category B - to not appear again within a 42 day period.
Category C - to not appear again within a 253 day period.

The aim is to count every Category A part once a month (have based this on an average of 21 working days in a month), Category B once every 2 months and Category C once a year.

The requirement is very similar to This thread just with less columns and rows, a different number of daily A's, B's and C's to be chosen and the option of data not appearing agan in a certain timeframe (if this is even possible?). I have tried to play around with the code provided in the thread, however I cannot get it to work.

Apologies if this information is not sufficeient or does not clearly explain the requirement.

I really apprecaite your help.

Thanks.

 

[Marc L]

​

As you can also link a workbook sample on a files host website like Dropbox for example …​

 

[Marc L]

​

And what is your strategy for this :​

If at all possible, the A's need to reappear every 21 days, the B's need to appear every 42 days and the C's once every 253 days.

 

[Peter_SSs]

C - rows 332 to 539

Category C - to not appear again within a 253 day period.

The above appears impossible. There are only 208 rows of C and you are picking one per day. There will be nothing left to choose from on day 209 yet we can't reload the C's on that day because you want at least 253 days before a possible repeat.

 

[dchapman17]

The above appears impossible. There are only 208 rows of C and you are picking one per day. There will be nothing left to choose from on day 209 yet we can't reload the C's on that day because you want at least 253 days before a possible repeat.

Thanks - understand. This can be reset for the 209th day. It doesn't really matter if some of the C's are counted twice in a year.

​

And what is your strategy for this :​

Apologies if I have misunderstood the question:
The aim is to count every Category A part once a month (have based this on an average of 21 working days in a month), Category B once every 2 months and Category C once a year.
205 Category A's / 21 (average working days in a month) = 9.76 counts a day. If it makes it easier then 10 x A can be counted and then reset on the 21st day.

 

[Peter_SSs]

Further question since putting restrictions on 'random' choices will make those choices not fully random.

After 20 days of no repeat values, 200 'A' values will have been chosen, leaving 5 never yet chosen.
So, on the 21st day those 5 would get chosen but that leaves us with 5 still to choose for that day. We have a number of choices but the most obvious are ..

1. Look for the values chosen longest ago and use them to fill out the extra 5. That mean there are only a total of 15 choices for the 10 places and some values will keep getting chosen every 21 days in the same or similar group - not very random.
2. Once those last 5 have been put into todays choices, choose 5 more from all other values. This certainly makes the choices more random, but it also means that the same value could be chosen two days running thereby missing your "not to be chosen again within a 21 day period" by a long way.

Which do you prefer, or do you have another way you would like to address the issue?

 

[dchapman17]

Thanks for the reply Peter. Option 1 will be fine. As long as each value appears once within the 20 or 21 day period then it's fine for certain values to appear twice.

Thank you for your help.