I've added my code below, although I'm nowhere close to my desired task.
I'm trying to sort ws1 in a way that Column A (which contains either YES, NO, MDR, or DPL) will be in order of all of the YES & MDR together, followed by all of the NO, with DPL sifted throughout as required. From here, the YES and MDR should be in alphabetical order by name in column B. And the NO should be in alphabetical order by name after the YES and MDR group. To confuse it even more, there are some duplicate names with DPL in Column A. These should be fit in with the matching name, with no care for whether the matching name is YES, NO, or MDR. Lastly, for any name with DPLs attached to it, Column E should be used to sort them in chronological order, oldest date on top. I know this is quite confusing to read. Here's an example that I hope explains it better. btw, the data set starts in Row 7.
<tbody>
</tbody>
I'm trying to sort ws1 in a way that Column A (which contains either YES, NO, MDR, or DPL) will be in order of all of the YES & MDR together, followed by all of the NO, with DPL sifted throughout as required. From here, the YES and MDR should be in alphabetical order by name in column B. And the NO should be in alphabetical order by name after the YES and MDR group. To confuse it even more, there are some duplicate names with DPL in Column A. These should be fit in with the matching name, with no care for whether the matching name is YES, NO, or MDR. Lastly, for any name with DPLs attached to it, Column E should be used to sort them in chronological order, oldest date on top. I know this is quite confusing to read. Here's an example that I hope explains it better. btw, the data set starts in Row 7.
| A | B | E |
| YES | Cole, Mike | 2 Feb 2019 |
| DPL | Cole, Mike | 16 Feb 2019 |
| MDR | Hotel, Jim | 4 Feb 2019 |
| DPL | Hotel, Jim | 9 Feb 2019 |
| YES | Jones, Sam | 2 Feb 2019 |
| YES | Lupo, Mike | 2 Feb 2019 |
| MDR | Patches, Tim | 2 Feb 2019 |
| YES | Smith, John | 2 Feb 2019 |
| DPL | Smith, John | 10 Feb 2019 |
| DPL | Smith, John | 28 Feb 2019 |
| NO | Adams, Matt | 2 Feb 2019 |
| NO | Baker, Kyle | 2 Feb 2019 |
| DPL | Baker, Kyle | 19 Feb 2019 |
| DPL | Baker, Kyle | 22 Feb 2019 |
| NO | Phelps, Tom | 4 Feb 2019 |
| NO | Roman, Tyler | 2 Feb 2019 |
<tbody>
</tbody>
Code:
Sub sortCol()
Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim vSortList As Variant
Dim LrC As Long
Dim x As Integer
ws1.Sort.SortFields.Clear
LrC = ws1.Range("A" & Rows.Count).End(xlUp).Row
vSortList = Array("YES", "MDR", "NO", "DPL")
On Error Resume Next
Application.AddCustomList ListArray:=vSortList
ws1.Range("A7:Y" & LrC).Sort Key1:=[E7], Order1:=xlAscending 'Date
ws1.Range("A7:Y" & LrC).Sort Key1:=[A7], ordercustos:=Application.CustomListCount + 1
ws1.Range("A7:Y" & LrC).Sort Key1:=[B7], Order1:=xlAscending 'Name
x = Application.GetCustomListNum(ListArray:=vSortList)
Application.DeleteCustomList x
ws1.Sort.SortFields.Clear
End Sub
