Ranking from Highest to Lowest

[flexinau]

Hi all,

Hope all of you are having a great day/night.

Trying to rank a listing from highest to lowest.

I am having issues with the items with zero values as there are more than one zeroes.

I need the final list to go from highest to lowest and list the items with zeroes last.

Must go from.......
Item A 8
Item B 1
Item C 5
Item D 4
Item E 0
Item F 0

....... to become like this
Item A 8
Item C 5
Item D 4
Item B 1
Item E 0
Item F 0

I still want to see Item E and Item F

I use Large function but it cannot handle the last two items with zeroes...

Has anyone worked out a solution for this?

Thanks

 

[mole999]

Excel Workbook
	A	B	C
1	Item A 8	8	1
2	Item B 1	1	4
3	Item C 5	5	2
4	Item D 4	4	3
5	Item E 0	0	5
6	Item F 0	0	5
Sheet1

 

[flexinau]

Thanks very much Mole999

Is there any way to get the last two items with 0 value to rank as 5 & 6 instead of 5 & 5?

Reason is I want to relist the table in rank order so I need the ranking to be unique.

 

[mole999]

there are posts about that explain methods, I wouldn't use them as they are identical, and therefore equal, For your needs you'd only need to sort the values, highest to lowest, then add an expanding list of numbers to drag down 1 onwards

 

[admiral100]

Hi,

You can try this

C2 =LARGE($B$2:$B$7,ROW(A1))
D2 =IFERROR(INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7=C2,IF(COUNTIF($D$1:D1,$A$2:$A$7)=0,ROW($A$2:$A$7)-ROW($A$2)+1)),1)),"")

D2 It's an array formula so Control+Shift+Enter not just Enter

Before A1	Before B1	After C1	After D1
Item A	8	8	Item A
Item B	1	5	Item C
Item C	5	4	Item D
Item D	4	1	Item B
Item E	0	0	Item E
Item F	0	0	Item F

<tbody>
</tbody>

 

[Aladin Akyurek]

ITEM	VALUE		ITEM	VALUE
Item A	8		Item A	8
Item B	1		Item C	5
Item C	5		Item D	4
Item D	4		Item B	1
Item E	0		Item E	0
Item F	0		Item F	0

<tbody>
</tbody>

In D2 control+shift+enter, not just enter, and copy down:

=IF($E2="","",INDEX($A$2:$A$7,SMALL(IF($B$2:$B$7=$E2,ROW($A$2:$A$7)-ROW($A$2)+1),COUNTIFS($E$2:E2,E2))))

In E2 just enter and copy down:

IF(ROWS($E$2:E2)>ROWS($A$2:$B$7),"",LARGE($B$2:$B$7,ROWS($E$2:E2)))

 

[flexinau]

Thanks mole999...
Thanks for your persistence admiral100 & Aladin Akyurek...this has provided me with a solution...

 

[Aladin Akyurek]

Thanks mole999...
Thanks for your persistence admiral100 & Aladin Akyurek...this has provided me with a solution...

You are welcome. Note that the set up I provided won't eliminate multiple occurrences of an item...