Explanation
The newdynamic array formulasinExcel 365make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes.
The randomness is handled by theRANDARRAY function, a new function in Excel 365.
Also, because RANDARRAY is avolatile function, it will recalculate with each worksheet change.
With this configuration, TEXTJOIN simplyconcatenatesall values together and returns a 6-character text string like “NEUGYC”.
This can be done with theCHAR functionand theSEQUENCE function.
Line breaks added forreadability.
The CHAR function translates the numeric value to a letter.
As above, all results are concatenated together in a single text string.
The range or array used to sort does not need to appear in results.
RANDARRAY Function
The Excel RANDARRAY function generates an array of random numbers between two values.
The size or the array is specified byrowsandcolumnsarguments.
The generated values can be either decimals or whole numbers.
COUNTA does not count empty cells.
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array.
The array can be one dimensional, or two-dimensional, determined byrowsandcolumnsarguments.
…
CHAR Function
The Excel CHAR function returns a character when given a valid character code.
CHAR can insert characters that are hard to enter into a formula.
