LARGE with same numbers

[papajohn]

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

 

[Andrew Poulsom]

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

>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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	3	19	X	19.1		1	Y	20
2	1	20	Y	20.2		2	Z	19
3	2	19	Z	19.3		3	X	19
4
5
Sheet3

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