Here is an empirical / Monte-Carlo type solution. It randomly populates the output columns with the set-A and set-B values and iterates until it finds a solution with no row duplicates.
To use this workbook, make sure you have macros enabled. Populate your set-A and set-B numbers into A3:B27. Click the "get Solution" button. Your 5 columns by 45 rows of set-A/B numbers without row duplicates will be written to M3:Q47
Details . . .
A3:A27 holds the 25 "Set-A" values
B3:B27 holds the 25 "Set-B" values
rand() / rank() functions provide a randomly sorted set of unique values in the range 1 to 225 in H3:H227
The range I3:I227 provides a column index into A3:B27. Col-I indices in the range 1 to 125 will yield a column index of 1 corresponding to the five required sets of group-A items. Col-I indices in the range 126-225 will yield a column index of 2 corresponding to the four required sets of group-B items. In I3 copied down to I227:
The range J3:J227 provides a row index into A3:B27. The 225 Col-J indices are populated with the required 5 repetitions of the 25 group-A items and the required 4 repetitions of the 25 group-B items. In J3 copied down to J227:
The formula for the five column, 45 row output area first gets the row/col indices in I:J and then uses them to index into the 25 set-A and 25 set-B values. In M3 then copied across and down to Q47:
As a validation check, cols C and D count how many occurrences of each A-set value and B-set value are present in the five column output area. The A-count must be 5 for each value and the B-count must be 4 for each value if this aspect of the output columns population is valid.
We're looking for no duplicate values in any of the 45 rows of the output area. This is the part that is done (automatically) by trial and error. Col-R contains a check for row duplicates. In R3 copied down to R47:
This is an array formula and assumes that the A and B set are numeric as opposed to text. Cell R48 contains a count of how many rows have duplicates. It needs to be zero for a valid solution.
Here's the VBA that basically loops around "calculate" until there are zero row duplicates.
To run it, first set calculations to manual Ribbon > Formulas > Calculation > Manual. Now click the "Get Solution" button that is located around cell N50. It typically takes a few thousand iterations and a minute or so to find a valid solution.
The "A solution" worksheet captures one example of a valid solution.
See the attached workbook. Note that due to the bit of VBA you must have macros enabled.
I'm not sure whether or not this type of solution works for your needs. Let us know.
Bookmarks