highest second highest and third highest

[ernie]

Hello..

I have one lists of number in A1:A7 and a list of words in B1:B7. I need to
find the highest, second highest and third highest value in these lists and
display the value and the word next to it in cell C1,C2, D1,D2 and E1,E2
respectively. However, there are same values for highest, second highest and
third highest in these list. If the value is the same, can I have it
separated?

For example: A1 B1
100 Car
100 Bus


Assuming both 100 is the highest. I want both Car and Bus to be shown in
cell C1. Possible ?Or any other solutions? Please kindly advise. Thank you.

help me

 

[ernie]

HELP ME! THANKS

 

[DubboPete]

HELP ME! THANKS
--
help me









- Show quoted text -

Ernie, be patient

This list always comes up with an answer, unless your request doesn't
make sense or there is not a logical solution!

Wait, your "help me" plea will probably be answered very soon!

Pete

 

[p45cal]

Hello..

I have one lists of number in A1:A7 and a list of words in B1:B7. need to
find the highest, second highest and third highest value in these list and
display the value and the word next to it in cell C1,C2, D1,D2 an E1,E2
respectively. However, there are same values for highest, secon highest and
third highest in these list. If the value is the same, can I have it
separated?

For example: A1 B1
100 Car
100 Bus


Assuming both 100 is the highest. I want both Car and Bus to be show in
cell C1. Possible ?Or any other solutions? Please kindly advise. Than you.

help me

Something like this:
490
(If you can't see the image it's at thecodecage.com, there's usually
link to it at the bottom of this message)
I thought at first this would be easy with just worksheet formulae bu
it proved not to be.

So here's a user defined function solution.
The formula should be used thus in a worksheet:
=Highest($A$1:$B$7,1,"words")
where:
$A$1:$B$7 is your range of numbers and words, with numbers on the left
Actually, if you only specify one column it will assume the next colum
to the right contains the words.

1 represents that you want the first highest. Replace with 2, 3
whatever, for 2nd and 3rd higher results. This can be a reference to
cell containing that number if you want, or a calculated value.

"words" signifies that you want the list of words in the cell. Us
"number" to signify you want to see the number instead. (Actually, th
function only responds to "number" to return the number (upper/lowe
case doesn't matter), if the string is anything else it will return th
word(s).)

So to get the third highest number you'd use, in any cell:
=Highest($A$1:$B$7,3,"number")

By the way, the errors in cells D6:E6 in the picture are there to sho
you what happens when you look for the nth highest when there isn't a
nth highest.

This formula needs to be supported by the function itself in a standar
module in the workbook:



VBA Code:
--------------------


Function Highest(theRange As Range, Rank As Long, Output As String)
Set col1 = theRange.Columns(1)
Set col2 = theRange.Columns(2)
incr = 1
If Rank > 1 Then
For i = 1 To Rank - 1
incr = incr + Application.CountIf(col1, Application.Large(col1, incr))
Next i
End If
If UCase(Output) = "NUMBER" Then
Highest = Application.Large(col1, incr)
Else
zz = Evaluate("IF(" & "'" & theRange.Parent.Name & "'!" & col1.Address & "=large(" & "'" & theRange.Parent.Name & "'!" & col1.Address & "," & incr & "),'" & theRange.Parent.Name & "'!" & col2.Address & ","""")")
Highest = Replace(Application.Trim(Join(Application.Transpose(zz), " ")), " ", ", ")
End If
End Function

--------------------


+-------------------------------------------------------------------+
|Filename: 2010-03-15_123726.png |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=490|
+-------------------------------------------------------------------+

 

[Chip Pearson]

The following formula will return the value in column B corresponding
to the highest value in A. In this example, the data is assumed to be
in A2:B8.

=INDEX(A2:B8,MATCH(LARGE(A2:A8,1),A2:A8,0),2)

For the second highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,2),A2:A8,0),2)

For the third highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,3),A2:A8,0),2)

Or, you could use a single array formula:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2)

Select the three cells in the same row in which you want to have the
results, type the following formula and press CTRL SHIFT ENTER

Note that the 1,2,3 is enclosed in curly braces, not parentheses. This
is an array formula, so you MUST press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula in the
formula bar enclosed in curly braces { }. You don't type in the
braces; Excel includes them automatically. The formula will not work
correctly if you do not use CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

If you want the results to be in three cells on the same row, use the
formula above. If you want the results in cells in the same column
spanning multiple rows, either TRANSPOSE the values:

=TRANSPOSE(INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2))

or change the commas that separate the 1,2,3 within the curly braces
to semicolons:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1;2;3}),A2:A8,0),2)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[p45cal]

The following formula will return the value in column B corresponding

to the highest value in A. In this example, the data is assumed to be
in A2:B8.

=INDEX(A2:B8,MATCH(LARGE(A2:A8,1),A2:A8,0),2)

For the second highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,2),A2:A8,0),2)

For the third highest, use

=INDEX(A2:B8,MATCH(LARGE(A2:A8,3),A2:A8,0),2)

Or, you could use a single array formula:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2)

Select the three cells in the same row in which you want to have the
results, type the following formula and press CTRL SHIFT ENTER

Note that the 1,2,3 is enclosed in curly braces, not parentheses. This
is an array formula, so you MUST press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it
later. If you do this properly, Excel will display the formula in the
formula bar enclosed in curly braces { }. You don't type in the
braces; Excel includes them automatically. The formula will not work
correctly if you do not use CTRL SHIFT ENTER. See
'Array Formulas' (http://www.cpearson.com/Excel/ArrayFormulas.aspx) fo much more information
about array formulas.

If you want the results to be in three cells on the same row, use the
formula above. If you want the results in cells in the same column
spanning multiple rows, either TRANSPOSE the values:

=TRANSPOSE(INDEX(A2:B8,MATCH(LARGE(A2:A8,{1,2,3}),A2:A8,0),2))

or change the commas that separate the 1,2,3 within the curly braces
to semicolons:

=INDEX(A2:B8,MATCH(LARGE(A2:A8,{1;2;3}),A2:A8,0),2)

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
'Excel Redirect' (http://www.cpearson.com)

Chip,
I was hoping yours was to be a simpler solution than my convolute
solution but when I tried versions of your formulae on the OP's data
where there are 2 duplicate highest numbers (100 for Car and Bus), both
=INDEX(A1:B7,MATCH(LARGE(A1:A7,1),A1:A7,0),2)
and
=INDEX(A1:B7,MATCH(LARGE(A1:A7,2),A1:A7,0),2)
returned Car, which is pretty much where I was coming adrift.
It seems to be because
LARGE(A1:A7,1)
and
LARGE(A1:A7,2)
both return 100.

(using XL2007)

 

[ernie]

sorry guys. I have tried your methods but it doesn't work. how do I paste the
VBA codes into my worksheet? p45cal ? Please kindly advise.

 

[ernie]

when i try =INDEX(A2:B8,MATCH(LARGE(A2:B8,1),A2:B8,0),2), it shows #N/A.

 

[p45cal]

sorry guys. I have tried your methods but it doesn't work. how do
paste the

VBA codes into my worksheet? p45cal ? Please kindly advise.

Take a look 'here' (http://www.contextures.com/xlvba01.html#Regular
under' Copy Excel VBA Code to a Regular Module'.

Or 'here' (http://www.rondebruin.nl/code.htm) under 'Paste the code i
a General, Regular or Standard module' ..

and/or 'here' (http://www.mvps.org/dmcritchie/excel/getstarted.htm
where the 'Problems' section may help if at first it doesn't seem t
work.

 

[ernie]

THANKS ALOT. I got exactly what I need from your formula and VBA code.

You are really good at this p45cal. Thanks again.