RANDBETWEEN per transaction id

[Ada01]

Hello

I want to generate data based on sales/transaction ID. Each transaction can have more than one sales line (per department). In trying to generate the department ID, I need to have any random number between 1,25 that does not repeat for any of the lines where the transaction id is the same

transaction id = 3200129
3200129 invoice lines = 2
for each line, randbetween 1,25 (that does not repeat)

1,18,23 (for example).

I am open to VBA to solve this issue if a formula cannot. I have not tried to write the code for that yet as I am working to get some security access to portions of office locked down by corporate policy.


Thanks

Adam.

 

[DanteAmor]

Try this VBA.
Exception: If an ID has more than 25 sales line, then the macro will randomize only the first 25. If it doesn't exceed 25, there will be no problem.
Note: The data does not need to be sorted by Trans_Id. The macro resolves the random numbers regardless of whether the data is ordered or not.

Your data as you have it in your image, the IDs in column A, the results in column C, and the names of the departments in column J, all starting in row 2.

Sub Random_Per_ID_v1()
  Dim dic As Object
  Dim arr, a, b, c, d, ky
  Dim i&, nrow&, ncol&, x&, y&
  Randomize
 
  Set dic = CreateObject("Scripting.Dictionary")
  arr = [row(1:25)]                                       'total Depts
  a = Range("A2", Range("A" & Rows.Count).End(3)).Value   'Ids
  b = Range("J2", Range("J" & Rows.Count).End(3)).Value   'Dept Names
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 1))           'Dept Unique
  ReDim d(1 To UBound(a, 1), 1 To 1)                      'Dept Random Name
 
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 1)) Then
      dic(a(i, 1)) = dic.Count + 1 & "|" & 1
    End If
    nrow = Split(dic(a(i, 1)), "|")(0)
    ncol = Split(dic(a(i, 1)), "|")(1)
    c(nrow, ncol) = i
    ncol = ncol + 1
    dic(a(i, 1)) = nrow & "|" & ncol
  Next
 
  For Each ky In dic.keys
    nrow = Split(dic(ky), "|")(0)
    ncol = Split(dic(ky), "|")(1) - 1
    If ncol > UBound(arr) Then ncol = 25
    For i = 1 To ncol
      x = Int((UBound(arr) - i + 1) * Rnd + i)
      y = arr(i, 1)
      arr(i, 1) = arr(x, 1)
      arr(x, 1) = y
      d(c(nrow, i), 1) = b(arr(i, 1), 1)
    Next i
  Next
 
  Range("C2").Resize(UBound(d)).Value = d
End Sub

 

[Ada01]

Hey DanteAmor

This does work, but I am running into an issue with the memory limits of my machine. In smaller chunks of data, this works out with no problems. Thanks for your efforts. I will continue with smaller sets of data and then bring them together.

Adam