Format Excel Worksheet With VBA

One way:

Option Explicit

Sub sReformat()

Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row

Range("A1").EntireColumn.Insert

Dim vArray, i As Long, j As Long
vArray = Range("A1:B" & lLR)

j = 0
For i = LBound(vArray) + 1 To UBound(vArray)
    If vArray(i, 2) <> vArray(i - 1, 2) Then
        j = j + 1
    End If
    vArray(i, 1) = "sf_" & j
Next 'i
    
With Range("A1")
    .Resize(UBound(vArray), 2) = vArray
    .Value = "sfID"
End With

End Sub

Oops, missed the sort:

Option Explicit

Sub sReformat()

Dim lLR As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
With ActiveWorkbook.Worksheets("Sheet1")
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=Range("A2:A" & lLR) _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A2:A" & lLR)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

Range("A1").EntireColumn.Insert

Dim vArray, i As Long, j As Long
vArray = Range("A1:B" & lLR)

j = 0
For i = LBound(vArray) + 1 To UBound(vArray)
    If vArray(i, 2) <> vArray(i - 1, 2) Then
        j = j + 1
    End If
    vArray(i, 1) = "sf_" & j
Next 'i
    
With Range("A1")
    .Resize(UBound(vArray), 2) = vArray
    .Value = "sfID"
End With

Application.ScreenUpdating = True

End Sub

You're welcome.