LARGE with same numbers

papajohn

New Member
Joined
May 29, 2002
Messages
20
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Feb 10, 2002
Messages
11,654
>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
 

Forum statistics

Threads
1,143,748
Messages
5,720,625
Members
422,293
Latest member
camillel

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
Top