RANDARRAY Function

[MadMike1717]

I am trying to great a 10 x 10 cell grid that generates random numbers 1 through 10 and does not repeat those numbers within a column or row.

My operating system is Mac BigSur 11.5.1

I have attached an example in its simplest form. If possible I would also like to be able to enter a number in any given cell and have the entire grid populate with this same unique randomness.

Thanks for the help.

[#2]

@MadMike1717 

Unless I'm mistaken--a distinct possibility--since you're limiting yourself (and us) to 10 whole numbers, between 1 and 10, in 10 rows and 10 columns, haven't you basically laid out the possibilities in your "simple example"?

You could vary the sequence of rows or columns randomly, but not both at the same time.

Here's one approach, with which I'm not fully satisfied. This uses the dynamic array function SORT, which does require the most recent version of Excel. Maybe it'll point you in a useful direction

[#3]

@mathetes :Thanks for the quick response. I have used the sort function but as you have stated it does not allow me to do both rows and columns at the same time.

This is the formula that I used for the sort function:=SORTBY(SEQUENCE(10,,0,1),RANDARRAY((10)))

This worked great for the randomness but I still have numbers that are the same in rows and columns.

[#4]

@MadMike1717 

Just for the record, mine used SORT, not SORTBY

Again, I wonder if you haven't set yourself up for actually a fairly limited number of possible combinations precisely because the numbers must be whole integers between 1 and 10, in ten rows and ten columns.

Anyway, another Dynamic Array function to test out is UNIQUE, which can make sure that any one number appears only once. I'd be more confident of meaningful randomness if you allowed integers between 1 and 99, say.

Here's a video on the Dynamic Array functions, in case they're new to you.

https://www.youtube.com/watch?v=9I9DtFOVPIg