Assuming your table to sort has headers in row 1 and data starts from row 2
Assuming that data are paired (if a pair exists)
Find a free column at the right of the last column, for example column AL
Set in AL2 this formula:
Code:
=IF(OR(AND(C2=C3,AJ2<>AJ3),AND(C2=C1,AJ2<>AJ1)),"A-"&UPPER(C2)&REPT(" ",30-LEN(C2))&UPPER(AJ2)&REPT(CHAR(122),30-LEN(AJ2)),"Z-"&UPPER(C2)&REPT(" ",30-LEN(C2))&UPPER(AJ2)&REPT(CHAR(122),30-LEN(AJ2)))
Copy in a standard module of your vba project the following code:
VBA Code:
Sub SortWnWo()
Dim ZForm As String, SortCol As String, SortH As Long
'
SortCol = "A:AL" '<<< The columns to sort, including the Helper column
Sheets("TheSheet").Select '<<< The sheet with the data
'
ZForm = Range(SortCol).Cells(2, Range(SortCol).Columns.Count).Address
SortH = Range(SortCol).Resize(, Range(SortCol).Columns.Count - 1).Find(What:="*", After:=Range(SortCol).Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row - 1
'Clear current Formulas and re-set them:
Range(ZForm).Offset(1, 0).Resize(Rows.Count - 3, 1).ClearContents
Range(ZForm).Copy Range(ZForm).Offset(1, 0).Resize(SortH - 1, 1)
'Start Sort:
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(ZForm).EntireColumn, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range(SortCol)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'Range(ZForm).Offset(1, 0).Resize(SortH - 1, 1).ClearContents '** Remove extra formulas
End Sub
Adapt the two lines marked <<< to your situation
Then run Sub SortWnWo
Column AL will be used to sort the table; you can examine the content of this column, that have been set via the formula to better understand the approach used. If you prefer, the line marked ** can be "uncommented" (remove the initial single quotation mark) and the table will be cleared, except the formula in row 2.
