The question I have in reference to the below random alphabetical generator formulas in excel:
=CHAR(RAND()*26+65)
Does not require ATP to be installed
=CHAR(RANDBETWEEN(65,90))
ATP to be installed in versions of Excel prior to 2007
Is there a way to make this to where the letters don't repeat themselves? Actually what I am looking for is to generate 26 characters (like that if the alphabet) in the same column but all random and no duplicate characters. Or if there is anyway to do this can you provide some aid?
Try this...
There must be a "header cell" in the range and this header cell must not contain one of the letters. It can be an empty cell.
So, let's use cell A1 as the header cell and it'll be an empty cell.
Create this named formula:
- Name: Nums
- Refers to: =ROW(INDIRECT("1:26"))
Enter this array formula** in A2 and copy down to A27:
=CHAR(64+SMALL(IF(ISNA(MATCH(CHAR(64+Nums),A$1:A1,0)),Nums),INT(RAND()*(26-(ROWS(A$2:A2)-1)))+1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Note that the letters will change every time a calculation takes place.