Note: The other languages of the website are Google-translated. Back to English
or

Register

or

# Generate Unique Random lettes 'a' to 'z' for a row ?

7 months 3 days ago #2822 by mikeduke1290
Replied by mikeduke1290 on topic Generate Unique Random lettes 'a' to 'z' for a row ?
Excel is extremely poorly suited to this, but here is what you can do. Create a reference table that is 26x32. The first row is the letters of the alphabet. The rest of the table (31 cells below each letter) is all RAND().
Create a second table that is 20x31. The formula in cell A1 should be =INDEX(POSITIONS!\$A\$1:\$Z\$1,MATCH(LARGE(POSITIONS!\$A2:\$Z2,COLUMN()),POSITIONS!\$A2:\$Z2,0)).
Where POSITIONS!\$A\$1:\$Z\$1 are the letters and POSITIONS!\$A2:\$Z2 are the first row of random numbers. Copy this formula down and across, so every cell has a letter in it. None of the letters should repeat across the row.
Finally, create your 10x31 table. Creating the first row is a bit of a chore, since you must type CONCAT(LETTERS!A1, LETTERS!B1), then manually increment it (since just copying right would create overlapping pairs). After you finish that, you can copy down.

• surfmaster@gmail.com
• Topic Author
• Offline
1 year 2 months ago #2095 by surfmaster@gmail.com
Generate Unique Random lettes 'a' to 'z' for a row ? was created by surfmaster@gmail.com
I am working on a task that requires me to have unique random values from 'a to z'. (it could even be 1 to 26, so, either all numbers or all letters).
In one row + 26 columns, it would have 1 letter each from a to z, without any letter being repeated. (example image attached).

Is it possible to do this for 40,000 + rows? without copying the same sequence? i.e. each row, would have it's own random sequence and hence the chances of any two row being similar would be significantly low.

If anyone can help, it would be very much appreciated.

Thank you.