Formula to rank names based on value High to Lowest and ignore zeros

Lukma

Active Member
Joined
Feb 12, 2020
Messages
259
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guys
Am having slit trouble with this formula in column L 13 and M13
what i am trying to get is to Rank the Name according to highest value

My List name is in Column I13
My Sum Value is in Colum J13
My Ranks Value is in H13

but after applying the formula in Column L13 for the Name i need it to list accordingly without a gap but mine is having a gap after applying down the formula

Appreciate with a support

Regards

ILSP KPI Performance Tracking Master Data-1.xlsx
HIJKLM
1101-May-21
12RankDrilling Rig/Barges28
136Rig Shuweihat5.35Rig Hudairiyat6.64
1410Deep Driller-63.88  
15 Rig SMS Mariam Rig Butinah 6.52
163Rig Butinah 6.52Rig Makasib5.48
17 Rig SMS Mariam Rig Qarnin5.45
183Rig Butinah 6.52Rig Shuweihat5.35
1914Rig Junana2.27Rig Yemillah5.21
2019Compact Driller1.34Rig Al Gharbia4.56
219Rig Al Bzoom4.55Rig Al Bzoom4.55
2222Rig Al Yasat0.76Deep Driller-63.88
23 Rig Al Ghallan Rig Artabhatt-13.83
24 Barge Pride  Rig Marawwah3.02
2528Barge Bahia0.00Rig Essa2.61
26 Barge Shahama Rig Junana2.27
2726Barge Supporter 0.16Rig Al Lulu2.06
2816Rig SMS Faith 2.04Rig SMS Faith 2.04
2911Rig Artabhatt-13.83Rig High Island-71.74
3027Rig Al Ittihad0.14Rig Al Hail1.55
3125Rig Diyina0.18Compact Driller1.34
3212Rig Marawwah3.02Barge Shamal0.85
338Rig Al Gharbia4.56Rig Muhaiyimat0.76
347Rig Yemillah5.21Rig Al Yasat0.76
35 Barge Lulwa Deep Driller-30.53
3624Barge Scirocco0.52Barge Scirocco0.52
3717Rig High Island-71.74Rig Diyina0.18
3815Rig Al Lulu2.06Barge Supporter 0.16
3921Rig Muhaiyimat0.76Rig Al Ittihad0.14
4013Rig Essa2.61Barge Bahia0.00
4123Deep Driller-30.53  
4220Barge Shamal0.85  
4318Rig Al Hail1.55  
444Rig Makasib5.48  
45 Rig VKN-3   
465Rig Qarnin5.45  
471Rig Hudairiyat6.64  
48    
Summary ILSP Exceed Hours
Cell Formulas
RangeFormula
L13:L48L13=IFERROR(IF(ROW()-ROW($L$12)<=$J$12,INDEX($I$13:$I$47,MATCH(ROW()-ROW($L$12),$H$13:$H$47,0)),""),"")
M13:M48M13=IFERROR(IF(L13<>"",INDEX($J$13:$J$48,MATCH(ROW()-ROW($L$12),$H$13:$H$48,0)),""),"")
H13:H48H13=IFERROR(IF(J13=0,"",RANK(J13,IF($J$13:$J$48>0,$J$13:$J$48,0))+COUNTIF($J$13:$J48,$J13)-1),"")
J12J12=MAX(H13:H48)
J13:J48J13=IFERROR(1/(1/SUMIFS(ILSP_KPI_Tracking_Data_Entry!$Q$18:$Q$60000,ILSP_KPI_Tracking_Data_Entry!$O$18:$O$60000,'Summary ILSP Exceed Hours'!I13,ILSP_KPI_Tracking_Data_Entry!$Q$18:$Q$60000,">0",ILSP_KPI_Tracking_Data_Entry!$B$18:$B$60000,'Summary ILSP Exceed Hours'!$I$11)),"")
Cells with Data Validation
CellAllowCriteria
I11List=ILSP_KPI_Tracking_Data_Entry!$ACT$18:$ACT$209
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
It is not entirely clear what you do want as you are showing us what you don't want. :)

Could you fill in columns L & M manually with what you do want there and post it again?
I'm particularly interested in what you want done where rank numbers are missing (eg there is not a number 2 rank in column H) or where there are equal rank values (eg there are two number 3 ranks on col H)
 
Upvote 0
Question
In columns I:J i can see two instances of the pair
Rig Butinah - 6.52
Do you want both in L:M or only one?

M.
 
Upvote 0
Hi Guys
Thanks for your usual support and frankly i do really appreciate and learn more

list of Rig name in column I
Actual Time in Column J

What i need is to extract and rank the Rig Name according to there time from Highest Time to lowest to Column L : M and ignore any zeros time

I have place a sample of what i need in L:M Ranking from Highest Rig name & time to lowest Rigs name & Time without including the zeros

I hope this explain it all

Regards

ILSP KPI Performance Tracking Master Data-1.xlsx
HIJKLM
201-May-21
3RankDrilling Rig/BargesDrilling Rig/Barges
4Rig Shuweihat5.35Rig Hudairiyat6.64
5Deep Driller-63.88Rig Butinah 6.52
6Rig SMS MariamRig Makasib5.48
7Rig Butinah 6.52Rig Qarnin5.45
8Rig SMS MariamRig Shuweihat5.35
9Rig Butinah 6.52Rig Yemillah5.21
10Rig Junana2.27Rig Al Gharbia4.56
11Compact Driller1.34Rig Al Bzoom4.55
12Rig Al Bzoom4.55Deep Driller-63.88
13Rig Al Yasat0.76Rig Artabhatt-13.83
14Rig Al GhallanRig Marawwah3.02
15Barge Pride Rig Essa2.61
16Barge Bahia0.00Rig Junana2.27
17Barge ShahamaRig Al Lulu2.06
18Barge Supporter 0.16Rig SMS Faith 2.04
19Rig SMS Faith 2.04Rig High Island-71.74
20Rig Artabhatt-13.83Rig Al Hail1.55
21Rig Al Ittihad0.14Compact Driller1.34
22Rig Diyina0.18Barge Shamal0.85
23Rig Marawwah3.02Rig Muhaiyimat0.76
24Rig Al Gharbia4.56Rig Al Yasat0.76
25Rig Yemillah5.21Deep Driller-30.53
26Barge LulwaBarge Scirocco0.52
27Barge Scirocco0.52Rig Diyina0.18
28Rig High Island-71.74Barge Supporter 0.16
29Rig Al Lulu2.06Rig Al Ittihad0.14
30Rig Muhaiyimat0.76
31Rig Essa2.61
32Deep Driller-30.53
33Barge Shamal0.85
34Rig Al Hail1.55
35Rig Makasib5.48
36Rig VKN-3
37Rig Qarnin5.45
38Rig Hudairiyat6.64
Sheet2
Cells with Data Validation
CellAllowCriteria
I2List=ILSP_KPI_Tracking_Data_Entry!$ACT$18:$ACT$209
 
Upvote 0
Hi Marcelo

Thanks for letting me see that
Question
In columns I:J i can see two instances of the pair
Rig Butinah - 6.52
Do you want both in L:M or only one?

M.
Hi Marcelo

Thanks for letting me see that i think is repeated i only need one and it should be one and not two

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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