Rank the data

S

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
995
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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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

B
  • Question Question
Rank formula to include cells with error.
Replies
2
Views
599
bcom93
B
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
M
  • Solved
Automate insertion of row data
2
Replies
12
Views
478
myall_blues
myall_blues
S
  • Solved
Ranking Array need to move as per the data
2
Replies
16
Views
514
sksanjeev786
S
E
  • Solved
Ranking Invoices By Highest Amount By "Year to Date"
Replies
2
Views
142
ellison
E

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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