Ranking with multiple column

[sksanjeev786]

Hi Team

I have almost 50 tab data and I want to rank columns F, E, D, C, B high to low (if we have duplicate data in each F column or pre.)
so can we have a MACRO for this Each tab name Q6a,Q6b till Q6e , then Q7a,Q7b. till Q7e

let me know if you have any questions

Ranked Data.xlsx
	A	B	C	D	E	F
3		Q2 2020	Q3 2020	Q4 2020	Q1 2021	Q2 2021
4
5	Fidelity	11	9	11	11	10
6	Charles Schwab	6	5	5	5	5
7	Voya (F)	2	3	2	2	2
8	Prudential	1	1	1	1	1
9	Ameriprise	0	0	1	1	1
10	TIAA	0	0	1	1	1
11	T Rowe Price	1	0	0	1	0
12	John Hancock	0	0	0	0	0
13	Lincoln Financial	0	0	0	0	0
14	The Hartford	0	0	0	0	0
15	Principal Financial Group	0	0	0	0	0
16	Empower Retirement	0	0	0	0	0
17	Transamerica	0	0	0	0	0
18	Competitor Average	1	1	1	1	1
1

Thanks
Sanjeev

 

[richh]

Hi Sanjeev,

It sounds like you want to perform a custom sort along the lines of...

1. Column F High to Low
2. Column E High to Low
3. Column D High to Low
4. Column C High to Low
5. Column B High to Low

... and apply that sort to all 50 worksheets in your workbook at the same time. Is that correct? Or the other way I think you may want is to...

1. Figure out which row in each worksheet is the best based upon the sum or average of all the values between Column B to Column F
2. Sort Each worksheet based upon that rank

Is that correct?

Either way, here are two solutions that you may wish to use:

Sub SortRowsFtoB()

    For Each Sheet In Application.ThisWorkbook.Worksheets
        With Sheet
            'This IF only needed b/c I have two diff sets and solutions in the workbook.
            If .Name = "Sheet1" Or .Name = "Sheet2" Or .Name = "Sheet3" Then
                .Sort.SortFields.Clear
                
                .Sort.SortFields.Add2 Key:=Range("F:F") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                
                .Sort.SortFields.Add2 Key:=Range("E:E") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
            .Sort.SortFields.Add2 Key:=Range("D:D") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
            .Sort.SortFields.Add2 Key:=Range("C:C") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
            .Sort.SortFields.Add2 Key:=Range("B:B") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
            
            With .Sort
                .SetRange Range("A:F")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    End With
    Next Sheet
End Sub

Sub SortRowsOnAVG()
    For Each Sheet In Application.ThisWorkbook.Worksheets
        With Sheet
            If .Name = "Sheet4" Or .Name = "Sheet5" Or .Name = "Sheet6" Then
                .Sort.SortFields.Clear
                
                .Sort.SortFields.Add2 Key:=Range("G:G") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
                                
            
            With .Sort
                .SetRange Range("A:G")
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End If
    End With
    Next Sheet
End Sub