# Ranking from Highest to Lowest

#### flexinau

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Excel Workbook
ABC
1Item A 881
2Item B 114
3Item C 552
4Item D 443
5Item E 005
6Item F 005
Sheet1

#### flexinau

##### Board Regular
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

##### Well-known Member
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

##### Well-known Member
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>

Last edited:

##### MrExcel MVP
 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

##### Board Regular
Thanks mole999...

##### MrExcel MVP
Thanks mole999...

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

Replies
1
Views
292
Replies
3
Views
346
Replies
9
Views
488
Replies
7
Views
466
Replies
11
Views
2K

1,195,934
Messages
6,012,382
Members
441,693
Latest member

### 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.

### Which adblocker are you using?

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

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