Excel Function UNIQUE is not working on MAKEARRAY and on RANDARRAY (Multi-dimension array)

Alan Koh W.T 0
Viewed 26

I have question about UNIQUE functions for random number generations in multi-dimension array.

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

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

Here are the formula 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))

Are there any workaround on this ?(or any unknown hidden functions in Excel)

excel-formula excel arrays
1 Answers

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 Craner0
Related