Sort columns and create sequence number

[benjaminyaon]

hello VBA Expert,

i would like to write a VBA code to sort a data and then create a sequence.

Please see the screen short,
I would like to sort the department first then sort the name. after sorting this two column, I would like to create a sequence number for each name in the same department, reset the sequence number for different.

how can i do this?Help and please advise,

4658

[benjaminyaon]

attached the Excel file.

please help.

4659

[Bob Phillips]

Sub Reformat()
Dim Lastrow As Long

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("B6").Resize(Lastrow - 5, 5).Sort _
Key1:=.Range("B6"), Order1:=xlAscending, _
Key2:=.Range("C6"), Order2:=xlAscending, _
Header:=xlYes
.Range("F7").Resize(Lastrow - 6).FormulaR1C1 = "=IF(RC[-4]<>R[-1]C[-4],1,R[-1]C+1)"
End With
End Sub