# LARGE with same numbers

#### papajohn

##### New Member
I am trying to use LARGE with VLOOKUP to rank items in a list so that I can create a Pareto chart. Everything works fine unless two of the items have the same value. Then I get a repeated value rather than a ranking.
example
A1=19 B1=X
A2=20 B2=Y
A3=19 B3=Z

I am trying to get these automatically sorted in ascending numerical order without having to manually run a sort(in another cell range).

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Andrew Poulsom

##### MrExcel MVP
In a spare column enter this formula:

=A1+(COUNTIF(A\$1:A1,A1)>1)/10

and copy down as far as needed.

This adds decimal "counters" to the duplicates. Use that column for your dynamic sort.

#### Mark W.

##### MrExcel MVP
>Then I get a repeated value rather than a ranking.

20 is the largest value, and there's a 2-way tie for the 2nd largest value -- 19. This is a proper ranking. If you want to break this tie for lookup purposes consider using a value other than those in column A. For example, the formula...

=(A1&"."&ROW())+0

...will insure a unique value. Here's how it might be used...
Book1
ABCDEFGHIJKLM
1319X19.11Y20
2120Y20.22Z19
3219Z19.33X19
4
5
Sheet3

This message was edited by Mark W. on 2002-10-21 11:22

Replies
1
Views
77
Replies
3
Views
175
Replies
7
Views
562
Replies
2
Views
850
Replies
13
Views
313

1,182,136
Messages
5,933,852
Members
436,915
Latest member
Cygne volant

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