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

[Lukma]

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
	H	I	J	K	L	M
11		01-May-21
12	Rank	Drilling Rig/Barges	28
13	6	Rig Shuweihat	5.35		Rig Hudairiyat	6.64
14	10	Deep Driller-6	3.88
15		Rig SMS Mariam			Rig Butinah	6.52
16	3	Rig Butinah	6.52		Rig Makasib	5.48
17		Rig SMS Mariam			Rig Qarnin	5.45
18	3	Rig Butinah	6.52		Rig Shuweihat	5.35
19	14	Rig Junana	2.27		Rig Yemillah	5.21
20	19	Compact Driller	1.34		Rig Al Gharbia	4.56
21	9	Rig Al Bzoom	4.55		Rig Al Bzoom	4.55
22	22	Rig Al Yasat	0.76		Deep Driller-6	3.88
23		Rig Al Ghallan			Rig Artabhatt-1	3.83
24		Barge Pride			Rig Marawwah	3.02
25	28	Barge Bahia	0.00		Rig Essa	2.61
26		Barge Shahama			Rig Junana	2.27
27	26	Barge Supporter	0.16		Rig Al Lulu	2.06
28	16	Rig SMS Faith	2.04		Rig SMS Faith	2.04
29	11	Rig Artabhatt-1	3.83		Rig High Island-7	1.74
30	27	Rig Al Ittihad	0.14		Rig Al Hail	1.55
31	25	Rig Diyina	0.18		Compact Driller	1.34
32	12	Rig Marawwah	3.02		Barge Shamal	0.85
33	8	Rig Al Gharbia	4.56		Rig Muhaiyimat	0.76
34	7	Rig Yemillah	5.21		Rig Al Yasat	0.76
35		Barge Lulwa			Deep Driller-3	0.53
36	24	Barge Scirocco	0.52		Barge Scirocco	0.52
37	17	Rig High Island-7	1.74		Rig Diyina	0.18
38	15	Rig Al Lulu	2.06		Barge Supporter	0.16
39	21	Rig Muhaiyimat	0.76		Rig Al Ittihad	0.14
40	13	Rig Essa	2.61		Barge Bahia	0.00
41	23	Deep Driller-3	0.53
42	20	Barge Shamal	0.85
43	18	Rig Al Hail	1.55
44	4	Rig Makasib	5.48
45		Rig VKN-3
46	5	Rig Qarnin	5.45
47	1	Rig Hudairiyat	6.64
48
Summary ILSP Exceed Hours

Cell Formulas
Range	Formula
L13:L48	L13	=IFERROR(IF(ROW()-ROW($L$12)<=$J$12,INDEX($I$13:$I$47,MATCH(ROW()-ROW($L$12),$H$13:$H$47,0)),""),"")
M13:M48	M13	=IFERROR(IF(L13<>"",INDEX($J$13:$J$48,MATCH(ROW()-ROW($L$12),$H$13:$H$48,0)),""),"")
H13:H48	H13	=IFERROR(IF(J13=0,"",RANK(J13,IF($J$13:$J$48>0,$J$13:$J$48,0))+COUNTIF($J$13:$J48,$J13)-1),"")
J12	J12	=MAX(H13:H48)
J13:J48	J13	=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
Cell	Allow	Criteria
I11	List	=ILSP_KPI_Tracking_Data_Entry!$ACT$18:$ACT$209

 

[Peter_SSs]

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)

 

[Marcelo Branco]

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.

 

[Lukma]

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
	H	I	J	K	L	M
2		01-May-21
3	Rank	Drilling Rig/Barges			Drilling Rig/Barges
4		Rig Shuweihat	5.35		Rig Hudairiyat	6.64
5		Deep Driller-6	3.88		Rig Butinah	6.52
6		Rig SMS Mariam			Rig Makasib	5.48
7		Rig Butinah	6.52		Rig Qarnin	5.45
8		Rig SMS Mariam			Rig Shuweihat	5.35
9		Rig Butinah	6.52		Rig Yemillah	5.21
10		Rig Junana	2.27		Rig Al Gharbia	4.56
11		Compact Driller	1.34		Rig Al Bzoom	4.55
12		Rig Al Bzoom	4.55		Deep Driller-6	3.88
13		Rig Al Yasat	0.76		Rig Artabhatt-1	3.83
14		Rig Al Ghallan			Rig Marawwah	3.02
15		Barge Pride			Rig Essa	2.61
16		Barge Bahia	0.00		Rig Junana	2.27
17		Barge Shahama			Rig Al Lulu	2.06
18		Barge Supporter	0.16		Rig SMS Faith	2.04
19		Rig SMS Faith	2.04		Rig High Island-7	1.74
20		Rig Artabhatt-1	3.83		Rig Al Hail	1.55
21		Rig Al Ittihad	0.14		Compact Driller	1.34
22		Rig Diyina	0.18		Barge Shamal	0.85
23		Rig Marawwah	3.02		Rig Muhaiyimat	0.76
24		Rig Al Gharbia	4.56		Rig Al Yasat	0.76
25		Rig Yemillah	5.21		Deep Driller-3	0.53
26		Barge Lulwa			Barge Scirocco	0.52
27		Barge Scirocco	0.52		Rig Diyina	0.18
28		Rig High Island-7	1.74		Barge Supporter	0.16
29		Rig Al Lulu	2.06		Rig Al Ittihad	0.14
30		Rig Muhaiyimat	0.76
31		Rig Essa	2.61
32		Deep Driller-3	0.53
33		Barge Shamal	0.85
34		Rig Al Hail	1.55
35		Rig Makasib	5.48
36		Rig VKN-3
37		Rig Qarnin	5.45
38		Rig Hudairiyat	6.64
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
I2	List	=ILSP_KPI_Tracking_Data_Entry!$ACT$18:$ACT$209

 

[Lukma]

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