1

I have a question about UNIQUE function for random number generation in multi-dimension array.

unique not working As you can see, I try to generate an array with random and unique numbers of 1->100 inside 5X5 array.

I try both MAKEARRAY and RANDARRAY with the UNIQUE in hopes that it can generate random number, but it still produces duplicate numbers (highlighted in red).

Here are the formulas used:

=UNIQUE(MAKEARRAY(5,5,LAMBDA(row,col,RANDBETWEEN(1,100))),,TRUE)
=MAKEARRAY(5,5,LAMBDA(row,col,UNIQUE(RANDBETWEEN(1,100),,TRUE)))
=UNIQUE(RANDARRAY(5,5,1,50,TRUE))

Is there any workaround for this (or any unknown hidden function in Excel)?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Alan Koh W.T
  • 413
  • 4
  • 19
  • 1
    Someone will most likely come up with a more robust solution, though you could try `=INDEX(UNIQUE(RANDARRAY(100,,1,100,1)),SEQUENCE(5,5))`. The choice of 100 for `RANDARRAY`'s *[rows]* parameter should make the probability of less than 25 unique values being generated extremely small, though you could of course choose a larger value for that parameter so as to further reduce that probability. – Jos Woolley Sep 21 '22 at 14:21
  • @JosWoolley there are other formula out there but that is probably very close to the shortest. And the unique randarray approach is probably the best to ensure uniqueness in the whole array. I came up with: `=WRAPROWS(TAKE(UNIQUE(RANDARRAY(100,,1,100,1)),25),5)` – Scott Craner Sep 21 '22 at 14:28
  • The issue with UNIQUE, is that it looks at either the full row or the full column. and using it on an existing 5x5 matrix will not find doubles by cell. so you need to create a single column or row of unique values then morph that into a 5x5 grid. – Scott Craner Sep 21 '22 at 14:31
  • @ScottCraner I was hoping that someone might come up with a 'cleaner' solution which didn't involve first creating an arbitrarily larger one-dimensional array and relying on probability. – Jos Woolley Sep 21 '22 at 14:35
  • 1
    @JosWoolley I guess you could use: `=WRAPROWS(TAKE(SORTBY(SEQUENCE(100),RANDARRAY(100)),25),5)` – Scott Craner Sep 21 '22 at 14:38
  • Excellent! You could probably even get away with replacing 100 with 25 in that set-up. – Jos Woolley Sep 21 '22 at 14:40

1 Answers1

7

The issue with UNIQUE, is that it looks at either the full row or the full column. and using it on an existing 5x5 matrix will not find doubles by cell. so you need to create a single column or row of unique values then morph that into a 5x5 grid.

There are many approaches one can take.

First creating a unique vertical list of a large number of random numbers between 1 and 100:

=WRAPROWS(TAKE(UNIQUE(RANDARRAY(100,,1,100,1)),25),5)

The RANDARRAY creates a list of 100 numbers between 1 and 100, odds are that you will find at least 25 unique numbers in that list.

The second is to create a list of numbers between 1 and 100 and randomly sort them:

=WRAPROWS(TAKE(SORTBY(SEQUENCE(100),RANDARRAY(100)),25),5)

Both create a list of 25 unique number between 1 and 100 then morph them into a 5 x 5 grid.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81