Вот версия VBA. Формулы - плохой выбор для этого, так как вам понадобится фиксированный 'RandomID', хранящийся где-то. Также (в этом случае, возможно, это не важно), но если кто-то изменит имя (Брак или подобное), он изменит их UserID. Это решение дает фиксированный вывод, который не изменится при изменении рабочей книги, а будет изменяться только при изменении ячейки вручную. Он также проверяет уникальный идентификатор UID.
Public Sub GenerateUserIDs()
'Get a quantity of rows by checking B2 for the last non empty cell. Add 1 because we have a header and
'it won't be included
NumRows = Range("B2", Range("B2").End(xlDown)).Rows.Count + 1
'Loop through all our rows, starting at 2
For X = 2 To NumRows
'Check each row, if column A is empty
If IsEmpty(Range("A" & X)) Then
Dim FName, LName, UID, ProposedUID, MaxNumberOfLoops, NumberOfLoops
'If it is, get their first and last name
FName = Range("B" & X).Value
LName = Range("C" & X).Value
'Set a maximum number of loops (If it loops more than this, quit, or we have an infinite loop
'and Excel will crash)
MaxNumberOfLoops = 400
NumberOfLoops = 0
'Set the UID without the random number
UID = "AA_" & Left(FName, 3) & Left(LName, 3)
'Keep looping until we find a UID that doesn't already exist
Do
'Add a loop count
NumberOfLoops = NumberOfLoops + 1
'Check if we've exceeded the loops
If NumberOfLoops > MaxNumberOfLoops Then
'We have. Message box and quit!
MsgBox "Exceeded " & MaxNumberOfLoops
Exit Sub
End If
'Add the Random Number to our UID before testing if it exists
ProposedUID = UID & RandomBetween(100, 999)
Loop While CheckUIDExists(ProposedUID, NumRows)
'We're out of the loop, so our UID is indeed unique. Set it in column A of the current row
Range("A" & X).Value = ProposedUID
End If
'On to the next row!
Next
End Sub
Function RandomBetween(Low As Long, High As Long)
'Randomize the numbers (must be run to get a truly random number'
Randomize
'Get a number between our highest and lowest
RandomBetween = Int((High - Low + 1) * Rnd + Low)
End Function
Function CheckUIDExists(ProposedUID, NumRows)
'Again loop through all our rows
For i = 2 To NumRows
'Check if column A contains our proposed UID
If Cells(i, 1).Value = ProposedUID Then
'If it does, send True back, indicating it needs to run again
CheckUIDExists = True
'Quit early because we don't need to test any more rows
Exit Function
End If
'On to the next row...
Next i
'We've done the entire loop, so it doesn't exist, return a False, indicating we can use that UID
CheckUIDExists = False
End Function
Пока в UserID ничего нет, он будет автоматически заполнять его для вас
После запуска: