Multifaceted Sorting


Board Regular
May 28, 2013
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.

YESCole, Mike2 Feb 2019
DPLCole, Mike16 Feb 2019
MDRHotel, Jim4 Feb 2019
DPLHotel, Jim9 Feb 2019
YESJones, Sam2 Feb 2019
YESLupo, Mike2 Feb 2019
MDRPatches, Tim2 Feb 2019
YESSmith, John2 Feb 2019
DPLSmith, John10 Feb 2019
DPLSmith, John28 Feb 2019
NOAdams, Matt2 Feb 2019
NOBaker, Kyle2 Feb 2019
DPLBaker, Kyle19 Feb 2019
DPLBaker, Kyle22 Feb 2019
NOPhelps, Tom4 Feb 2019
NORoman, Tyler2 Feb 2019


Sub sortCol()

Dim ws1     As Worksheet: Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim vSortList     As Variant
Dim LrC     As Long
Dim x     As Integer


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


End Sub


Board Regular
May 28, 2013
Giving this a bump since I'm in a weird time zone. The best answers always show up after I go to bed!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...