Related:
- Special sort in excel
- How to sort bookmarks in chrome - Guide
- Special character letter - Guide
- Number to words in excel - Guide
- Gif in excel - Guide
- Marksheet in excel - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 26, 2010 at 12:58 AM
Mar 26, 2010 at 12:58 AM
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 ?
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.
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Mar 26, 2010 at 05:58 PM
Mar 26, 2010 at 05:58 PM
Try this
Assumptions:
Data is on sheet1
New sheet would be called sheet2
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