Rank the data

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
883
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Team,

I need ranking based on the column F Based on the row num F3 (including A1:F4),F8 (A6:F9),F13 (A11: F14) and so on

book2
ABCDEF
1Q. Slide 7
2Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
3Fidelity3634343336
4BASE1062112710379951037
5
6Q. Slide 7
7Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
8Charles Schwab1715191717
9BASE10591112999947983
10
11Q. Slide 7
12Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
13Principal Financial Group69151213
14BASE471501427419409
15
16Q. Slide 7
17Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
18TIAA1315151317
19BASE482479400351380
20
21Q. Slide 7
22Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
23MetLife0001415
24BASE000879909
Sheet1
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Could you please show what you want your expected results to look like? Here are a couple of guesses, which basically differ in how they handle ties:

Book1
ABCDEFGHIJKLMN
1Q. Slide 7NameQ2 2022RankNameQ2 2022Rank
2Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022Fidelity361Fidelity361
3Fidelity3634343336Charles Schwab172Charles Schwab172
4BASE1062112710379951037TIAA173TIAA172
5MetLife154MetLife154
6Q. Slide 7Principal Financial Group135Principal Financial Group135
7Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
8Charles Schwab1715191717
9BASE10591112999947983
10
11Q. Slide 7
12Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
13Principal Financial Group69151213
14BASE471501427419409
15
16Q. Slide 7
17Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
18TIAA1315151317
19BASE482479400351380
20
21Q. Slide 7
22Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
23MetLife0001415
24BASE000879909
25
Sheet9
Cell Formulas
RangeFormula
H2:J6H2=LET(a,SORT(FILTER(A1:F25,MOD(SEQUENCE(ROWS(A1:F25)),5)=3),6,-1),CHOOSE({1,2,3},INDEX(a,,1),INDEX(a,,6),SEQUENCE(ROWS(a))))
L2:N6L2=LET(a,SORT(FILTER(A1:F25,MOD(SEQUENCE(ROWS(A1:F25)),5)=3),6,-1),CHOOSE({1,2,3},INDEX(a,,1),INDEX(a,,6),MMULT(--(INDEX(a,,6)<TRANSPOSE(INDEX(a,,6))),SEQUENCE(ROWS(a),,,0))+1))
Dynamic array formulas.
 
Upvote 0
Eric W said:
Could you please show what you want your expected results to look like? Here are a couple of guesses, which basically differ in how they handle ties:

Book1
ABCDEFGHIJKLMN
1Q. Slide 7NameQ2 2022RankNameQ2 2022Rank
2Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022Fidelity361Fidelity361
3Fidelity3634343336Charles Schwab172Charles Schwab172
4BASE1062112710379951037TIAA173TIAA172
5MetLife154MetLife154
6Q. Slide 7Principal Financial Group135Principal Financial Group135
7Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
8Charles Schwab1715191717
9BASE10591112999947983
10
11Q. Slide 7
12Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
13Principal Financial Group69151213
14BASE471501427419409
15
16Q. Slide 7
17Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
18TIAA1315151317
19BASE482479400351380
20
21Q. Slide 7
22Q2 2021Q3 2021Q4 2021Q1 2022Q2 2022
23MetLife0001415
24BASE000879909
25
Sheet9
Cell Formulas
RangeFormula
H2:J6H2=LET(a,SORT(FILTER(A1:F25,MOD(SEQUENCE(ROWS(A1:F25)),5)=3),6,-1),CHOOSE({1,2,3},INDEX(a,,1),INDEX(a,,6),SEQUENCE(ROWS(a))))
L2:N6L2=LET(a,SORT(FILTER(A1:F25,MOD(SEQUENCE(ROWS(A1:F25)),5)=3),6,-1),CHOOSE({1,2,3},INDEX(a,,1),INDEX(a,,6),MMULT(--(INDEX(a,,6)<TRANSPOSE(INDEX(a,,6))),SEQUENCE(ROWS(a),,,0))+1))
Dynamic array formulas.
Click to expand...
Hi Eric,

Thank you so much for your help but I need data in the same format how is A1 to F25 I need re-rank with same format based on the F3 (including A1:F4),F8 (A6:F9),F13 (A11: F14) and so on
 
Upvote 0
Please show what you want your expected results to look like? Where do you want the ranking? How do you want to handle ties? Can you use Excel 365 functions (like LET), or do I need to only use Excel 2016 functions?
 
Upvote 0
Eric W said:
Please show what you want your expected results to look like? Where do you want the ranking? How do you want to handle ties? Can you use Excel 365 functions (like LET), or do I need to only use Excel 2016 functions?
Click to expand...

Hi Eric,

Sorry for delay

Here is the updated data after ranking
Q. Slide 7
Fidelity3634343336
BASE
1006.4​
1018.5​
966​
918.8​
927.7​
Q. Slide 7
Empower Retirement3630262529
BASE
118.3​
129.2​
106.9​
135.9​
148.4​
Q. Slide 7
Charles Schwab1715191717
BASE
1003.4​
1007.9​
930.1​
874.4​
882.2​
Q. Slide 7
TIAA
13​
15​
15​
13​
17​
BASE
457.8​
437.8​
374.7​
320.8​
350.2​
Q. Slide 7
MetLife
0​
0​
0​
14​
15​
BASE
0​
0​
0​
809.8​
815.2​
Q. Slide 7
Principal Financial Group69151213
BASE
446.9​
452.7​
397.3​
387.4​
373.8​
Q. Slide 7
Voya (F)79101013
BASE
710.7​
662.8​
578.1​
587.5​
581.3​
Q. Slide 7
T Rowe Price
11​
9​
10​
11​
11​
BASE
772.4​
770.7​
676.2​
620.5​
610.8​
Q. Slide 7
Lincoln Financial67111010
BASE
592.2​
596.5​
543.3​
525.1​
531.6​
Q. Slide 7
Prudential9910910
BASE
908.1​
921.1​
839​
772.9​
784.1​
Q. Slide 7
Transamerica56789
BASE
523.8​
548.7​
455.7​
436.5​
414.3​
Q. Slide 7
The Hartford55798
BASE
793.5​
776.5​
678​
660.1​
645.2​
Q. Slide 7
John Hancock77777
BASE
810.1​
800​
695​
674.3​
683.9​
Q. Slide 7
Competitor1313141515
Base85689522833079568081
 
Upvote 0
So you basically want the table sorted, right?

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

VBA Code: 
Sub SortGroups()
Dim lr As Long, i As Long, j As Long, k As Long, mydat As Variant, sorttab() As Variant, outtab As Variant

    lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
    If (lr Mod 5) <> 0 Then
        MsgBox ("The number of rows (with trailing space) must be a multiple of 5.")
        Exit Sub
    End If
    
    mydat = Range("A1:F" & lr).Value
    ReDim sorttab(1 To lr / 5, 1 To 2)
    For i = 1 To lr / 5
        sorttab(i, 1) = i
        sorttab(i, 2) = mydat(i * 5 - 2, 6)
    Next i
    
    sorttab = WorksheetFunction.Sort(sorttab, 2, -1)
    
    ReDim outtab(1 To UBound(mydat), 1 To 6)
    For i = 1 To lr / 5
        For j = 0 To 3
            For k = 1 To 6
                outtab(i * 5 - 4 + j, k) = mydat(sorttab(i, 1) * 5 - 4 + j, k)
            Next k
        Next j
    Next i
    
    Range("H1").Resize(UBound(outtab), 6) = outtab
    
End Sub

This macro assumes that the data starts in A1, is 6 columns wide, and each entry has 5 rows (counting a trailing blank row). The last line says where the results should go. If you just want it to replace the original table, put A1 in the last line instead of H1.

Then press Alt-Q to close the VBA editor. Press Alt-F8 to open the macro selector. Select SortGroups and click Run.

Let me know how this works!
 
Upvote 0
Solution
Eric W said:
So you basically want the table sorted, right?

Open a copy of your workbook. Press Alt-F11 to open the VBA editor. Press Alt-IM to Insert a Module. Paste the following code in the window that opens:

VBA Code: 
Sub SortGroups()
Dim lr As Long, i As Long, j As Long, k As Long, mydat As Variant, sorttab() As Variant, outtab As Variant

    lr = Cells(Rows.Count, "A").End(xlUp).Row + 1
    If (lr Mod 5) <> 0 Then
        MsgBox ("The number of rows (with trailing space) must be a multiple of 5.")
        Exit Sub
    End If
   
    mydat = Range("A1:F" & lr).Value
    ReDim sorttab(1 To lr / 5, 1 To 2)
    For i = 1 To lr / 5
        sorttab(i, 1) = i
        sorttab(i, 2) = mydat(i * 5 - 2, 6)
    Next i
   
    sorttab = WorksheetFunction.Sort(sorttab, 2, -1)
   
    ReDim outtab(1 To UBound(mydat), 1 To 6)
    For i = 1 To lr / 5
        For j = 0 To 3
            For k = 1 To 6
                outtab(i * 5 - 4 + j, k) = mydat(sorttab(i, 1) * 5 - 4 + j, k)
            Next k
        Next j
    Next i
   
    Range("H1").Resize(UBound(outtab), 6) = outtab
   
End Sub

This macro assumes that the data starts in A1, is 6 columns wide, and each entry has 5 rows (counting a trailing blank row). The last line says where the results should go. If you just want it to replace the original table, put A1 in the last line instead of H1.

Then press Alt-Q to close the VBA editor. Press Alt-F8 to open the macro selector. Select SortGroups and click Run.

Let me know how this works!
Click to expand...

Waw...!!!!!!!!

Thank you so muchhhhhhhh Eric :):)
 
Upvote 0
Eric W said:
Happy to help! 😎
Click to expand...

I wanted to improve my macro i don't know how to start could you please guide me on how to start or improve?
whenever i start start seeing the video able to do those tasks. but if I want something as per my requirement not able to perform that task.

Please guide me I wanted to become like you and how you can do anything ......
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Calculate Year-to-date figures taken from another table in one cell based on selecting a month in a dropdown list
Replies
3
Views
434
Alex Blakenburg
A
N
  • Question
rank function tie breaker
Replies
3
Views
230
Fluff
Fluff
jski21
  • Solved
Find last entry in column and sum all prior rows
Replies
3
Views
132
Fluff
Fluff
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
153
bobaol
B
B
  • Solved
VBA Loop for data transfer
Replies
6
Views
188
Joe4
Joe4

Forum statistics

Threads
1,215,176
Messages
6,123,470
Members
449,100
Latest member
sktz

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top