Allocating Unique characters

[prasadmoghe.pm]

Query:
I have 1 to 5 number in five rows from A1 to A5 and I need to allocate randomly 2 alphabets from a to z for each number in cells B1 & C1. The condition is that the alphabets should not get repeated for any number and across the numbers. E.g

1 a b
2 c d
3 e f
4 g h
5 i j

I have tried using INT and RAND() function with array of alphabets but there is a possibility of duplicates.

Thank you in advance.

Regards,
Prasad

 

[circledchicken]

Hi Prasad,

Try this:

1. In each of the cells D1 to D26 enter "=RAND()"
2. In cell B1 enter "=CHAR(96+RANK(D1,$D$1:$D$26))" and drag the formula down to cell B5
3. In cell C1 enter "=CHAR(96+RANK(D6,$D$1:$D$26))" and drag the formula down to cell C5

• The RAND function produces random numbers between 0 and 1.
• The RANK function ranks the random numbers in column D returning unique integers from 1-26.
• The CHAR function returns a character based on the ASCII value (97 to 122 are the letters a-z).

For more about random selection in Excel, the below resource is great:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/

 

[anandvarma]

Hi Prasad,

Try this:

1. In each of the cells D1 to D26 enter "=RAND()"
2. In cell B1 enter "=CHAR(96+RANK(D1,$D$1:$D$26))" and drag the formula down to cell B5
3. In cell C1 enter "=CHAR(96+RANK(D6,$D$1:$D$26))" and drag the formula down to cell C5

• The RAND function produces random numbers between 0 and 1.
• The RANK function ranks the random numbers in column D returning unique integers from 1-26.
• The CHAR function returns a character based on the ASCII value (97 to 122 are the letters a-z).

For more about random selection in Excel, the below resource is great:
http://www.tushar-mehta.com/excel/newsgroups/rand_selection/

have a look at MRAND in Morefunc addin,
here you can have randum numbers without duplicates

 

[prasadmoghe.pm]

Hi Anand
Thanks for responding quickly. I will try this function and let you know.
Cheers,
Prasad