I can't do it with a formula but I can do it with a Macro.
Open the VBA Editor (If you don't know how to do that, Hold Alt and Press F11)
In the VBA Project on the left hand side, righ-click in a space, then click Insert -> Module, then in the VBA window that pops up, paste the below code and then close the VBA module.
When in your spreadsheet and you want to sort your data, Hold ALT and press F8, then click the macro SORT and click on RUN.
Hope this helps.
Code:
Sub Sort()
'
' Sort Macro
'
'Creates a start point to come back to later as I don't know the name of your worksheet
'Replace "A1" and "A:A" below with the cell reference of the Label at the top of the column that needs sorting
Dim rng As Range
Set rng = Range("A1")
rng.Name = "Start_Here"
'Filters the data to remove duplicates
Columns("A:A").Select
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy
'Adds a new sheet to put the data into
With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
End With
'Pastes the data
ActiveSheet.Paste
Application.CutCopyMode = False
'Sorts the data
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range( _
"A2:A1048568"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveSheet.Sort
.SetRange Range("A1:A1048568")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Goto Reference:="Start_Here"
Columns("A:A").Select
ActiveSheet.ShowAllData
Selection.ClearContents
Application.Goto Reference:="Temp!R1C1"
'Remember to replace "A:A" below with the relevant column
Columns("A:A").Select
Selection.Copy
'Pastes the data back where it came from
Application.Goto Reference:="Start_Here"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
"A2:A1048568"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:A1048568")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Deletes the "Temp" sheet
Application.DisplayAlerts = False
Sheets("Temp").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.Goto Reference:="Start_Here"
End Sub
