Random sets without repeats to completion

[radm1f]

Hello all.

I am looking to create a matrix of RNG numbers 8 rows and 8 columns from 1 through 8 without any repeats in any column or row. It would be great if the function "calculate now" in excel would regenerate different randomization of this matrix. Any ideas? I have tried Rand() then rank function but that is only one column or row to get it to happen in 8 rows and columns without repeating has had me at a loss.

Thank you so very much.

[Olly]

Here's one approach, using VBA iterations of random numbers in a listobject table.

Please Login or Register  to view this content.

It will try up to 1000 times per cell to find a valid random value which doesn't already exist in the intersecting row / column; then reset the table and attempt again, if a valid value is not found..

This code also logs the result, number of attempts, and time taken.

I repeated 50 times - every time I got a successful matrix result. It took an average of 36.1 attempts, taking an average of 3.9 seconds to generate a successful 8x8 matrix.

[radm1f]

I am so sorry for sounding dense. I do not use VBE and I am not familiar with it. Do I just copy and Paste this code in a module in VBE and run it? I tried that and I get the message "Run-time error '9': Subscript out of range" The script line in the code that is highlighted is "Set lo = Sheet1.ListObjects(1)"

Thank you for your help in this.

M.

[Olly]

Apologies - I intended to attach a workbook to demonstrate. Attached to this post.

This code relies on having a Table, with 8 columns, and 8 rows, on worksheet Sheet1.

The logging function also needs a Table with 3 columns on worksheet shLog - you can easily remove and ignore this bit, it was just for performance testing.

[radm1f]

Thank you so very much Olly. It works perfectly. I cannot thank you enough.

M.