Excel Random Text in column from a set of options without duplicates

Try:

Please Login or Register  to view this content.

This code is just like pressing F9 and checking if there were duplicates in column B - if yes pressing F9 again and again until no duplicates were observed.
As a matter of fact duplicates are checked after each random selection, because there is no point to draw next value (in next row) if current one is already a duplicate :-)

Thanks for the prompt reply, the solution is perfectly working for the range mentioned. However, it becomes an endless loop when I am working with rows close to 30.

For more convenience, I am attaching a sample sheet which I would be using for my purpose. In this attached sheet, the code was ran for an approximately 30 minutes, and eventually I had to stop this code to arrive a solution for these number of rows.

This is actually a timetable for a week, where the faculty codes are to be placed in columns, so that there are no duplicates in any column.

https://app.box.com/s/awfq4q8xgfb9lkqoj4x5bv0v2kskqu77

I was wondering, how this could be accomplished when there are 30 rows and 21 columns are to be checked for proper ordering (from the options available) with no duplicate values in any column. Yes, “X” or “Hdy” can be an exception. The check that each prefix count (P,C,B,or Z) has to be a fixed number for any batch with-in a week , which would also be given from Col AX to Col BE.

Requesting for inputs on this.

Regards

There is no chance to do it as you described.
In BS3:BV3 you have the same values (empty cells) as in BS21:BV21 and some further rows.
So empty cells will always repeat.

PS. Data is very heavily restricting possible solutions.
For instance RM-05E shall be given P2 and RM-15E shall be C6 because there is no other rows with P2 or C6 available.
RM-11E shall be either C7 or Z4 - one of these 2 will always remain unselected.
If we include this information, RM-16H shall be B6 (the only place where B6 can be used), then RM-22E shall be C8 and RM-09E shall be B5
then RM-20H and RM-13E shall be asssigned (in any sequence) C2 and P7.
etc.
See sheet Arkusz1
It is reasonably easy with functions and manual selection, but to have it fully automatic with macro is (at least for me) not so easy.

And I am not surprised totally random solution searc gave no effect after half an hour. possible assignments number is 4^19 while number of acceptable solutions is probably just few dozens. so we have probability around one billionth :-(

Thankyou Kaper for responding and performing such an analysis on this.

Till now, we have been preparing such a timetable manually (for around 60 batches, under different groups), and it takes a lot of pain from various inputs to prepare this and consumes almost a day. That is why we look for automation of this timetable.

Yes, it is very well taken the probability is very low to arrive at some possible solutions. Instead of doing it manually, can we put some constraints on this, to reduce the denominator of total permutations of arranging Faculty code for any column? For example:

1. As there are 3 classes for a batch (1,2,3), and faculty codes with prefixes are (P,C,B,Z), can we fix up like, “P” code shall be taking 1st class, 2nd class shall be booked for “C” Code or some other?, depending upon our choice order in some matrix (in tabular form, Class Preference order Matrix) i.e. from Column BF to BH.

2. A helper column has been introduced which is about the count of each faculty code allocated to different batches. Probably this shall help in shuffling of Faculty Codes (P1,P2….. etc.) to some extent, i.e. more the count in column (BJ, extended upto Col BM), implies that code can be placed in more number of batches.

3. In a row, for a week, the maximum number of lectures possible is 21 for any batch, the different allocation of number of faculty codes for a week can be obtained from (AF to AI).

4. Adding a "Hdy" "Holiday" for any day for any class may not put any faculty code on this, which shall further minimize the denominator.

Seeing this much of permutations, yes, it is very much essential to reduce denominator first, then calculate the options available.

Regards

I do think it is rather time consuming, so at the momentI will not continue in this thread.
Anaybody willing to participate in looking for solution is kindly welcome to join in.
Other idea could be to publish the question in http://www.excelforum.com/commercial-services/ subforum

1) Mod is a not good name - there is operator mod in VBA, so to avoid confusion fo user and for VBA compliator/interpreter use different sub name (like myMod, etc.)
2) Rank for P&i (which for instance for P3 is empty ) out of Range("P" & i & ":" & "S" & i) (most are empty, and S&i seems to be related to next day