Special sort in excel Closed

Question

I want to make a results sheet in excel for a competition that will create an automatically sorted list so that it can be quickly and easily uploaded to a blog.

Is there a way to take data from an unsorted table,

NAME CATEGORY SCORE

and have it automatically sort into a neat list on another sheet, first by category, then by high score?

ie.

ADVANCED CATEGORY 

name  4000
name  3000
name  2000
etc.

BEGINNER CATEGORY

name  2500
name  2000
name  1500

UNDER 14

etc.
etc...

rizvisa1 · Answer

How one can sort of category ? I mean are the category worded in such way that they will sort alphabetically the way you want.
Also 

On this new sheet, are you converting  three columns NAME CATEGORY SCORE 

into 
CATEGORY 1
NAME  SCORE 


CATEGORY 2
NAME  SCORE 


CATEGORY 3
NAME  SCORE 

or is it still three columns ?

UlethClimbing · Answer

All the data will be converted converted into: 

CATEGORY 1 
NAME SCORE 


CATEGORY 2 
NAME SCORE 


CATEGORY 3 
NAME SCORE 

It will not be in alphabetical order, but I need to be able to specify the order of the categories, and the names in each category need to be sorted from from high to low score.

If it helps, these are the categories and the order they need to be sorted in:

MENS ADVANCED
WOMENS ADVANCED
MENS BEGINNERS
WOMENS BEGINNERS
UNDER 14 BOYS
UNDER 14 GIRLS

There will be a max of 100 participants with their scores divided into these categories.

rizvisa1 · Answer

Try this
Assumptions:

Data is on sheet1
New sheet would be called sheet2


Sub genReport()

Dim sortOrder() As Variant
Dim lMaxRows As Long
Dim item As Variant
Dim lbeanCounter As Long
Dim oSheet As String
Dim nSheet As String

    oSheet = "Sheet1"
    nSheet = "Sheet2"
    
    sortOrder = Array("MENS ADVANCED", "WOMENS ADVANCED", "MENS BEGINNERS", "WOMENS BEGINNERS", "UNDER 14 BOYS", "UNDER 14 GIRLS")

    Sheets(oSheet).Select
    
    Cells.Select
    If ActiveSheet.AutoFilterMode Then Selection.AutoFilter


    If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter
    lbeanCounter = 1
    
    For Each item In sortOrder
    
        Selection.AutoFilter Field:=2, Criteria1:="=" & item, Operator:=xlAnd, Criteria2:="<>"
        
        lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
        
        If lMaxRows > 1 Then
        
            Range("A2:A" & lMaxRows).Select
            Selection.Copy
            
            Sheets(nSheet).Select
            
            Cells(lbeanCounter + 1, "A").Select
            ActiveSheet.Paste
                      
            Sheets(oSheet).Select
            Range("C2:C" & lMaxRows).Select
            Selection.Copy
   
            Sheets(nSheet).Select
            
            Cells(lbeanCounter + 1, "B").Select
            ActiveSheet.Paste
            
            Cells(lbeanCounter, "A") = item
            
            lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
            
            Range(Cells(lbeanCounter + 1, "A"), Cells(lMaxRows, "B")).Select
            
            Selection.Sort _
                Key1:=Range("B" & lbeanCounter + 1), Order1:=xlDescending, _
                Key2:=Range("A" & lbeanCounter + 1), Order1:=xlAscending, _
                Header:=xlNo, OrderCustom:=1, _
                MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal

            lbeanCounter = Cells(Rows.Count, "A").End(xlUp).Row + 2
            
            Sheets(oSheet).Select
            
        End If
        
    Next item
    
    Cells.Select
    If ActiveSheet.AutoFilterMode Then Selection.AutoFilter
    Range("A1").Select
    
End Sub

Special sort in excel

3 responses

Subscribe To Our Newsletter!