Need Excel to generate a random unique static number based on a specific age division

Originally Posted by clark402

Thank you Bo_Ry. Unfortunately, there were duplicates within an age division. Every contestant needs a unique contestant number.

Fixed

Sub Urand()
Dim a, l&, i&, st&, m&
a = [LET(z,F3:G8,n,INDEX(z,,2),s,--MID(LEFT(n,FIND("-",n)-1),FIND("ween",n)+5,3),CHOOSE({1,2,3},INDEX(z,,1),s,RIGHT(n,3)-s+1))]
l = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To l
    If Cells(i, 4) = "" Then
        st = Application.VLookup(Cells(i, 1), a, 2, 0)
        m = Application.VLookup(Cells(i, 1), a, 3, 0)
        Cells(i, 4) = Evaluate("LET(div,A2:A" & l & ",id,D2:D" & l & ",m," & m & ",l,FILTER(id,(div=""" & Cells(i, 1) & _
            """)*isnumber(id),0),a,SEQUENCE(m,," & st & "),n,m-COUNT(l)+sum(-(a=13)),SMALL(FILTER(a,ISNA(MATCH(a,l,))*(a<>13)),RANDBETWEEN(1,n)))")
    End If
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Or Target.Column = 4 Then
        Application.EnableEvents = False
        Urand
        Application.EnableEvents = True
    End If

End Sub