Private Sub CommandButton1_Click()
Dim i As Integer
Dim finalRow, uqfinalRow As Long
finalRow = Range("E" & Rows.Count).End(xlUp).Row
For i = 2 To finalRow
Range("A" & i).Select
ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[3],C[11],C[10],""Not Found"",0)"
Range("B" & i).Select
ActiveCell.FormulaR1C1 = _
"=PROPER(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=FALSE,LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""."",RC5))))-1),IF(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=TRUE,IF(ISERROR(FIND(""_"",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1)" & _
")))),TRUE))=FALSE,LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""_"",RC5))))-1),IF(IF(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=TRUE,IF(ISERROR(FIND(""_"",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE))=TRUE,IF(ISERROR(FIND(""-"",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND" & _
"(""@"",RC5))))-1))))),TRUE))=FALSE,LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""-"",RC5))))-1),LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1)))))" & ""
Range("C" & i).Select
ActiveCell.FormulaR1C1 = _
"=PROPER(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=FALSE,RIGHT(LEFT(RC5,FIND(""@"",RC5)-1),LEN(LEFT(RC5,FIND(""@"",RC5)-1))-FIND(""."",RC5)),IF(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=TRUE,IF(ISERROR(FIND(""_"",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-F" & _
"IND(""@"",RC5))))-1))))),TRUE))=FALSE,RIGHT(LEFT(RC5,FIND(""@"",RC5)-1),LEN(LEFT(RC5,FIND(""@"",RC5)-1))-FIND(""_"",RC5)),IF(IF(IF(IF(ISERROR(FIND(""."",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE)=TRUE,IF(ISERROR(FIND(""_"",((LEFT(RC5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE))=TRUE,IF(ISERROR(FIND(""-"",((LEFT(R" & _
"C5,LEN(RC5)-LEN((RIGHT(RC5,LEN(RC5)-FIND(""@"",RC5))))-1))))),TRUE))=FALSE,RIGHT(LEFT(RC5,FIND(""@"",RC5)-1),LEN(LEFT(RC5,FIND(""@"",RC5)-1))-FIND(""-"",RC5)),""""))))" & ""
Range("D" & i).Select
ActiveCell.FormulaR1C1 = "=PROPER(LEFT(REPLACE(RC[1],1,FIND(""@"",RC[1]),""""),FIND(""."",REPLACE(RC[1],1,FIND(""@"",RC[1]),""""))-1))"
Next
Range("D:D").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("L1"), Unique:=True
Range("L:L").Sort Key1:=Range("L2"), order1:=xlAscending, Header:=xlNo
uqfinalRow = Range("L" & Rows.Count).End(xlUp).Row
Range("K2").Activate
For i = 1 To uqfinalRow - 1
If ActiveCell.Offset(-1, 0).Value = "Code" Then
ActiveCell.Offset(0, 0).Value = "1"
Else
ActiveCell.Offset(0, 0).Value = "=@INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1"
End If
ActiveCell.Offset(1, 0).Select
Next i
Range("B2:D" & finalRow).Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("K2:K" & uqfinalRow).Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
Range("A" & finalRow).Offset(1, 0).Select
End Sub