Sort Data By Group

Hi,

I already tried romper's solution here, which was the simplest, but I got confused and couldn't do it.

http://www.excelforum.com/excel-gene...-in-excel.html

I need to sort by Adviser Fee. I guess the groups are self explanatory, but yeah, any help would be mad as.

Despite appearances, it isn't from a Pivot. I just manually typed the data there. The real data looks similar but is many rows long of just manually entered data.

I can sort your data, if I add two additional columns used for sorting only
as follows:
a. The first column is a sequence number used to keep items in order after
the sort, and also can be used to restore your data to it's original order.
b. The second column is a concatenation of the 'Advisor Fee' (with additional
leading Zeros added as required, depending on the largest fee) and the
User Name.

For example the additional columns in your sample data would be:
00002 00000050.00Daniel
00003 00000050.00Daniel
00004 00000050.00Daniel
00005 00000015.00Simon
00006 00000015.00Simon
00007 00000015.00Simon
00008 00000020.00Gerry
00009 00000020.00Gerry
00010 00000020.00Gerry

You would sort with the second column as the primary sort, and
the first column as the secondary sort. I couldn't find foolproof
formulas to create the data and keep the data after sorting, so I went
the Macro route.

See the attached Worksheet (written and tested in Excel 2003), but it should
work in your version. I included the following Macros that can be activated by
'Buttons' on your worksheet:
a. Clear Columns F and G.
b. Add Sequence Numbers in Column F and Advisor Fee / Client Name in Column G.
c. Sort by Sequence Number
d. Sort by Advisor Fee / Client Name

Option Explicit

Private Const nStartROW = 2

Private Const sClientNameCOLUMN = "A"
Private Const AdvisorFeeCOLUMN = "C"
Private Const sSequenceCOLUMN = "F"
Private Const sFeeAndClientNameCOLUMN = "G"

Sub ClearGroupSortData()

  Dim sRange As String
  
  sRange = sSequenceCOLUMN & ":" & sFeeAndClientNameCOLUMN
  Range(sRange).ClearContents

End Sub


Sub PrepareForGroupSort()

  Const myRGB_Pretty_Pink = 13408767    'Obtained through anecdotal evidence

  Dim myRGB_Color As Long
  Dim iLastRow As Long
  Dim irow As Long
  
  Dim sAdvisorFee As String
  Dim sClientName As String
  Dim sData As String
  

  'Clear the Extra Columns
  Call ClearGroupSortData

  'Insert Header data
  irow = 1
  Cells(irow, sSequenceCOLUMN) = "Sequence"
  Cells(irow, sFeeAndClientNameCOLUMN) = "FeeAndName"

  'Find the last row in the Worksheet
  iLastRow = GetLastRowSheet(ActiveSheet.Name)

  'Loop through the data
  For irow = nStartROW To iLastRow
  
    'Get contents and color of the  'Client Name' cell
    sData = Cells(irow, sClientNameCOLUMN).Text
    myRGB_Color = Cells(irow, sClientNameCOLUMN).Interior.Color
  
    'Process only if the cell has data
    If Len(sData) > 0 Then
    
      'Get the new 'Client Name' and 'Advisor Fee' if column 1 is 'Pretty Pink'
      If myRGB_Color = myRGB_Pretty_Pink Then
        sClientName = Trim(Cells(irow, sClientNameCOLUMN).Text)
        sAdvisorFee = Trim(Cells(irow, AdvisorFeeCOLUMN).Text)
        
        'Format the 'Advisor Fee' for sorting Purposes
        sAdvisorFee = Format(sAdvisorFee, "00000.00")
      End If
      
      'Set the 'Sequence' value for the row (Leading "'" makes it text and not a number)
      Cells(irow, sSequenceCOLUMN) = "'" & Format(irow, "0000000")
    
      'Set the Fee And Advisor value for the row
      Cells(irow, sFeeAndClientNameCOLUMN) = sAdvisorFee & sClientName
    End If
    
  Next irow

End Sub

Sub SortBySequenceNumber()

  Dim iLastRow As Long
  
  Dim sRange As String
  
  'Find the last row in the Worksheet
  iLastRow = GetLastRowSheet(ActiveSheet.Name)
  
  sRange = "A1:G" & iLastRow
  Range(sRange).Sort _
    Key1:=Range(sSequenceCOLUMN & "1"), Order2:=xlAscending, _
    Header:=xlYes, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

Sub SortByAdvisorFeeAndClientName()

  Dim iLastRow As Long
  
  Dim sRange As String
  
  'Find the last row in the Worksheet
  iLastRow = GetLastRowSheet(ActiveSheet.Name)
  
  sRange = "A1:G" & iLastRow
  Range(sRange).Sort _
    Key1:=Range(sFeeAndClientNameCOLUMN & "1"), Order1:=xlAscending, _
    Key2:=Range(sSequenceCOLUMN & "1"), Order2:=xlAscending, _
    Header:=xlYes, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal
End Sub

Function GetLastRowSheet(SheetName As String) As Long
  GetLastRowSheet = Sheets(SheetName).UsedRange.Row - 1 _
                  + Sheets(SheetName).UsedRange.Rows.Count
End Function