Special sort in excel

Closed
Report
-
 UlethClimbing -
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...

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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 ?

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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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

Thank you for the code, but I cannot for the life of me make it work for myself.

What sheet is this going on?
How am I supposed to input my data in order for it to work?