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

[Surfmaster]

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.

 

[mikerickson]

I think this will do what you want.
Currently it does 5 rows, but that can be adjusted.

Sub test()
    Dim Alphabet(1 To 26) As String
    Dim i As Long, temp As String, randindex As Long
    Dim rowCount As Long
   
    For i = 1 To 26
        Alphabet(i) = Chr(i + Asc("a") - 1)
    Next i
   
    For rowCount = 1 To 5: Rem adjust <<<<<<<<<<<<<<
   
        For i = 1 To 26
            randindex = Int(26 * a) + 1
            temp = Alphabet(i)
            Alphabet(i) = Alphabet(randindex)
            Alphabet(randindex) = temp
        Next i
       
        Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 26).Value = Alphabet
   
    Next rowCount
   
End Sub

 

[Surfmaster]

Wow.... this macro works like a charm.

One question though:.. Right now it is maintaining the a to z sequence. Is there any way to randomize this? so instead of a b c d e... type of output, the output could be a x v p h.. etc (i..e total random sequence)?

 

[Scott Huish]

I see the problem with that code, try this:

Sub test()
    Dim Alphabet(1 To 26) As String
    Dim i As Long, temp As String, randindex As Long
    Dim rowCount As Long
   
    Randomize
   
    For i = 1 To 26
        Alphabet(i) = Chr(i + Asc("a") - 1)
    Next i
  
    For rowCount = 1 To 5: Rem adjust <<<<<<<<<<<<<<
  
        For i = 1 To 26
            randindex = Int(26 * Rnd()) + 1
            temp = Alphabet(i)
            Alphabet(i) = Alphabet(randindex)
            Alphabet(randindex) = temp
        Next i
      
        Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 26).Value = Alphabet
  
    Next rowCount
  
End Sub

 

[Surfmaster]

Thank you Scott, that worked just right

Mike & Scott, both of you are awesome & a life saver.. Thanks a lot.

I really appreciate it..

 

[Surfmaster]

I see the problem with that code, try this:

Sub test()
    Dim Alphabet(1 To 26) As String
    Dim i As Long, temp As String, randindex As Long
    Dim rowCount As Long
  
    Randomize
  
    For i = 1 To 26
        Alphabet(i) = Chr(i + Asc("a") - 1)
    Next i
 
    For rowCount = 1 To 5: Rem adjust <<<<<<<<<<<<<<
 
        For i = 1 To 26
            randindex = Int(26 * Rnd()) + 1
            temp = Alphabet(i)
            Alphabet(i) = Alphabet(randindex)
            Alphabet(randindex) = temp
        Next i
     
        Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 26).Value = Alphabet
 
    Next rowCount
 
End Sub

Hi Scott,

Another question if you don't mind.

If I wanted to change the output so instead of a to z, each row would have the random output of 1 to 31. (=> there will be 31 columns instead of 26)...

How would I be able to do it using the above Macro? I tried layman's approach, but any edits that I did, didn't produce the output

Thanks.

 

[DanteAmor]

How about

Sub test2()
  Dim arr As Variant, i As Long, temp As String, randindex As Long
  Dim rowCount As Long
  Randomize
  arr = [row(1:31)]
  For rowCount = 1 To 5: Rem adjust <<<<<<<<<<<<<<
      For i = 1 To 31
          randindex = Int(31 * Rnd()) + 1
          temp = arr(randindex, 1)
          arr(randindex, 1) = arr(i, 1)
          arr(i, 1) = Val(temp)
      Next i
      Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 31).Value = Application.Transpose(arr)
  Next rowCount
End Sub

 

[Surfmaster]

How about

Sub test2()
  Dim arr As Variant, i As Long, temp As String, randindex As Long
  Dim rowCount As Long
  Randomize
  arr = [row(1:31)]
  For rowCount = 1 To 5: Rem adjust <<<<<<<<<<<<<<
      For i = 1 To 31
          randindex = Int(31 * Rnd()) + 1
          temp = arr(randindex, 1)
          arr(randindex, 1) = arr(i, 1)
          arr(i, 1) = Val(temp)
      Next i
      Range("A65536").End(xlUp).Offset(1, 0).Resize(1, 31).Value = Application.Transpose(arr)
  Next rowCount
End Sub

That works ... Awesome ..

Thanks a lot, much appreciated.

 

[DanteAmor]

Glad we could help & thanks for the feedback