I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

sbRandomNoRepeatBeforeN

If you want to perform a random pick on some items (names, numbers, whatever), and these items can occur more than once but you need them not to re-appear within the next N draws, the function shown here will help.

It calls sbRandHistoGrm which you will need to include into your VBA code.

This function is a fairly advanced example on how to use scripting dictionaries (associative arrays). It shows:
- How to attach values to names (keys)
- How to add, to remove and to change key / value pairs
- How to look up values
- How to access the whole value set as an array
- How to test whether a dictionary is empty (i.e. has no entries)
- How to access values numerically indexed (can be used to numerically index through the key or value set)

An example for N = 3:

sbRandomNoRepeatBeforeN_01_Screen

Please notice that for some data or for some random sequence a solution might be impossible. With the same input data as above but a different random sequence you can get:

sbRandomNoRepeatBeforeN_02_Screen

As you can see, the single "C" and "D" have been picked already and the additional "A"s or "B" would violate the gap condition of 3 cells in B5.

sbRandomNoRepeatBeforeN_01_Code

If you are interested in downloading a 22 KB Excel 2010 sample file go to my Download page, please.

sbRandomNoRepeatBeforeN_02_Code
Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download