Excel function UNIQUE is not working on MAKEARRAY and on RANDARRAY (multi-dimension array)
I have a question about UNIQUE function for random number generation in multi-dimension array.
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)?
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.
- Codeigniter parentheses in dynamic Active Record query
- How to send cart data in hidden field and fetch it in codeigniter controller
- Sorting an array of objects by a property
- Sorting objects in array
- Codeigniter Insert Array to Database
- How to display an array of values in a table with javascript
- C# ToArray conversion merged into one line with resulting array element change
- accessing values in an ARRAY STRUCT in Google Bigquery
- Sort an object array by a column
- Push a new object into a sorted objectArray at the correct position to avoid re-sorting
- How do you natsort() by a column of an array of objects?
- How to create JSON Schema for an array of dictionaries with different type values
- Naturally sort a flat array without preserving original keys
- Create array of values from a dictionary of array of objects filtered by property
- How can I sort an alphanumerical Multidimensional Array in php?
- Naturally sort the rows of a 2d array by a column of alphanumeric values
- Sort an array of alphanumeric strings naturally
- Sort an array of strings by their 3rd character, then 4th, then 2nd
- Sort array based only on trailing numbers in strings
- Custom sort a flat array of scalar values so that integers are given top priority
- sort() prints 1 instead of the sorted array
- How to naturally sort a flat array of filenames (some ending with numbers, e.g. "-2")?
- Is there a shortcut way to return an array sorted in C#
- How to implement multiple custom sorting rules based on hyphen-delimited substrings?
- How can I access data of object stored in an array?
- How to include OR in Array formula where ONLY SOME criteria need to be TRUE - Excel
- How to fix 'Private field must be declared in an enclosing class'
- How can I write a Zig function that can accept and return strings?
- Search for groups of cells that contain values, calculate the difference, place result in adjacent column
- Including more commands if CSV column is not empty