Random single letter/digit grid

[rtrnow61]

Hi,

Based on Mr. Excel's YouTube video showing how to create a random non-repeating letter grid I was wondering if anyone could help me create a random single letter or number grid for 26 letters of the alphabet by the number 0-9

Example: say A-Z are the columns and 0-9 are the rows and then I can generate a random letter or number (I would like the random generation to include both random letters A-Z and numbers 0-9) and non-repeating if possible.

I am an Excel novice and have a very specific use for this to create a secret number code corresponding to pricing. The non-repeat is not required but random is.

Thank you very much for any and all assistance.

 

[Rick Rothstein]

For what you are describing, you need a grid where the values in each column are all unique. The following two procedures (place both in the same module and then run the CreateUniqueTwoCharacterGrid macro) will create such a grid for you.

[table="width: 500"]
[tr]
	[td]Sub CreateUniqueTwoCharacterGrid()
  Dim C As Long, Arr As Variant
  Const Chars As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  Range("B1:AA1") = [TRANSPOSE(CHAR(ROW(65:90)))]
  Range("A2:A11") = [ROW(1:10)-1]
  Union([B1:AA1], [A2:A11]).Interior.Color = RGB(171, 171, 171)
  Range("B2:AA11").HorizontalAlignment = xlCenter
  Arr = Split(Left(StrConv(Chars, vbUnicode), 2 * Len(Chars) - 1), Chr(0))
  For C = 2 To 27
    Cells(2, C).Resize(10) = Application.Transpose(RandomizeArray(Arr))
  Next
  Columns("A:AA").AutoFit
End Sub

Function RandomizeArray(ArrayIn As Variant)
  Dim cnt As Long, RandomIndex As Long, Tmp As Variant
  Randomize
  For cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
    RandomIndex = Int((cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
    Tmp = ArrayIn(RandomIndex)
    ArrayIn(RandomIndex) = ArrayIn(cnt)
    ArrayIn(cnt) = Tmp
  Next
  RandomizeArray = ArrayIn
End Function[/td]
[/tr]
[/table]

 

[rtrnow61]

Hi Rick,

Thank you so much! I made the macro and it worked great and as you know, the macro made a grid that I was looking for.

Thank you so much.

 

[rtrnow61]

Thank you shg for your time and effort in working with me on this issue.

 

[Rick Rothstein]

Thank you so much! I made the macro and it worked great and as you know, the macro made a grid that I was looking for.

Thank you so much.

You are quite welcome. Just out of curiosity, do you need to track your cost down to the penny? If so, here is a slight revision to my code to add a row for the decimal point. This would allow you to code pennies if needed.

[table="width: 500"]
[tr]
	[td]Sub CreateUniqueTwoCharacterGrid()
  Dim C As Long, Arr As Variant
  Const Chars As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  Range("B1:AA1") = [TRANSPOSE(CHAR(ROW(65:90)))]
  Range("A2:A12") = [ROW(1:11)-1]
  Range("A12") = "."
  Union([B1:AA1], [A2:A12]).Interior.Color = RGB(171, 171, 171)
  Range("B2:AA12").HorizontalAlignment = xlCenter
  Arr = Split(Left(StrConv(Chars, vbUnicode), 2 * Len(Chars) - 1), Chr(0))
  For C = 2 To 27
    Cells(2, C).Resize(11) = Application.Transpose(RandomizeArray(Arr))
  Next
  Columns("A:AA").AutoFit
End Sub

Function RandomizeArray(ArrayIn As Variant)
  Dim cnt As Long, RandomIndex As Long, Tmp As Variant
  Randomize
  For cnt = UBound(ArrayIn) To LBound(ArrayIn) Step -1
    RandomIndex = Int((cnt - LBound(ArrayIn) + 1) * Rnd + LBound(ArrayIn))
    Tmp = ArrayIn(RandomIndex)
    ArrayIn(RandomIndex) = ArrayIn(cnt)
    ArrayIn(cnt) = Tmp
  Next
  RandomizeArray = ArrayIn
End Function[/td]
[/tr]
[/table]

 

[rtrnow61]

It might be useful Rick and I would be interested in seeing the revision in case I wanted to put it to use...

 

[Rick Rothstein]

It might be useful Rick and I would be interested in seeing the revision in case I wanted to put it to use...

I posted the revision in Message #24 (the message you are replying to).

 

[Rick Rothstein]

Assuming you are using the code I posted in Message #24 which contains the row with a decimal point (no problem being there if you decide not to use it), here are two functions that you may find helpful. The first function takes the your column letter (not Excel's) and the amount you want to encode as arguments and returns the code from that column for that amount. Note, if your amount has cents and the last digit is 0, then you need to quote the amount so that the trailing zero does not get lost (you don't have to do this for whole number amounts). The second function takes the column letter (again, your column letter, not Excel's) and the code as arguments and returns the original amount that was encoded for that column.

[table="width: 500"]
[tr]
	[td]Function GetCode(Letter As String, Amount As String) As String
  Dim X As Long, Col As Long
  Col = Range(Letter & 1).Column + 1
  For X = 1 To Len(Amount)
    If Mid(Amount, X, 1) = "." Then
      GetCode = GetCode & Cells(12, Col)
    Else
      GetCode = GetCode & Cells(Mid(Amount, X, 1) + 2, Col)
    End If
  Next
End Function

Function GetPrice(Code As String, Letter As String) As Variant
  Dim X As Long, Col As Long, ColCodes As String
  Col = Range(Letter & 1).Column + 1
  ColCodes = Join(Application.Transpose(Application.Index(Cells, [ROW(2:12)], Col)), "")
  For X = 1 To Len(Code)
    If Mid(Code, X, 1) = Right(ColCodes, 1) Then
      Mid(Code, X) = "."
    Else
      Mid(Code, X) = InStr(ColCodes, Mid(Code, X, 1)) - 1
    End If
  Next
  GetPrice = Code
End Function[/td]
[/tr]
[/table]

 

[rtrnow61]

Hi Rick,

Not certain I understand but will try. Do I just add the 2 new functions to the Macro with the decimal? Or do I only use 1 at a time and add it (edit) the decimal macro?

Thank you very much for all your assistance.

 

[Rick Rothstein]

Hi Rick,

Not certain I understand but will try. Do I just add the 2 new functions to the Macro with the decimal? Or do I only use 1 at a time and add it (edit) the decimal macro?

The idea for the GetCode and GetPrice functions is that you would use them after you had created the grid using the macro I gave you in Message #24 . Once the grid is created, I would not think you would need to use that macro again. As for the function, copy them into a code module (you can put them in the same one the macro is/was in). Now, with the grid sheet active, put this formula in a blank cell....

=GetCode("M","562.83")

It will display 9QU4V6... that is the code for 562.83 from (your) Column M. Later, if you want to see how much you paid for the item with that code, put this formula in a cell...

=GetPrice("9QU4V6","M")

and it will display 562.83 thus giving you a way to use the chart without having to figure things out manually.

 

[rtrnow61]

Wow! Great and thank you again, Rick. You have been so helpful and I very much appreciate your assistance.