0
Undo
Votes
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.
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.
0
Undo
Votes
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.
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.
- Page :
- 1
There are no replies made for this post yet.