Hello everyone,



Hope you can help me to the this using MSExcel 2007, (no RANDARRAY() function)

----------

Input cells:

A1 = any positive integer.
B1 = any nonnegative integer (but less than A1).
C1 = any positive integer that is greater than or equal to B1 (but less than A1).

Output cells:

A2, A3, A4, ..., contain the numbers 1, 2, 3, ..., (in order), until we reach the value of A1.

B2, C2, D2, ..., contain distinct random integers satisfying 4 properties:
* the number of columns must be a random integer between B1 and C1 (inclusively),
* each of these random integers must lie between 1 and A1 (inclusively),
* these numbers must be in ascending order,
* and none of these is equal to the value of A2.

Similarity for B3, C3, D3, ...
Similarity for B4, C4, D4, ...

and so on, until we reach to the last row in column A.

----------

Your help would be appreciated. Thanks.

In the attached, a single celled user defined function used thus:
2022-10-16_171134.png


Edit:Sorry, just noticed

Attached Files

msofficeforums49802.xlsm (16.0 KB, 5 views)

This should work for Excel 2007 but can't test.
To enter the formula, first select a range having a row count of the value in A1 and a column count of the value in C1 + 1, then enter
RandNos(A1,B1,C1)
then while holding both the Ctrl and Shift keys, press Enter to array-enter that formula in the whole range at once.
Of course the range size needs to change according to what's in A1 and C1, which can be irritating.
If you want, we could create a macro which would put the values in the cells instead of using a formula?

Attached Files

msofficeforums49802versionFor2007.xlsm (19.4 KB, 5 views)