Max

[Darin Kramer]

Hi There,

2 columns , A and B (A with animals, B with number of animals)

Cats 3
Dogs 2
Mice 6
Birds 3
Lions 0


I want the MAX three numbers - forumlae to do this is LARGE(B1:b5,{1})
(and similiar large... {2}... large {3} In the above eg gives a 6 as a
result) but how do I then translate that 6 into its description, ie
mice???
Vlookup wont work because you may have 2 hits, eg Cats and birds both
have 3 hits

Hope this isnt too confusing....

Thanks!!!

D

*** Sent via Developersdex http://www.developersdex.com ***

 

[Toppers]

This will return MICE

=INDEX(A1:B5,MATCH(LARGE(B1:B5,{1}),B1:B5,0),1)

 

[Toppers]

Sorry ... too quick with previous post. I don't know how to handle duplicate
counts without using VBA.

 

[Darin Kramer]

VBA is a possibility, (if its not too complicated )

What were u thinking..?

Thanks

D (again!)


*** Sent via Developersdex http://www.developersdex.com ***

 

[Toppers]

Darin,
Is it possible to sort data in descending order then just take
first 3 entries?

 

[Toppers]

This will find 3 largest and put names in array V ....


Sub Find3large()
Dim rng As Range
Dim v(3) As String
n = 0
Set rng = Selection
For i = 1 To 3
l = Application.Large(rng.Columns(2), i)
With rng.Columns(2)
Set c = .Find(l, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
n = n + 1
If n > 3 Then Exit For
v(n) = c.Offset(0, -1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next i
For i = 1 To 3
MsgBox v(i)
Next i

End Sub

 

[Andrew Taylor]

You can do this of thing without VBA, though it helps to have a few
extra columns for your working. Assuming your list starts in A1:

In C1: =RANK(B1,$B$1:$B$5) and copy down

this gives 2 for Cats & Birds so we have to modify it:

in D1: =C1+COUNTIF($C$1:C1,C1)-1 and copy down

this makes the second 2 (for birds) into a 3, because there's
a previous occurence of 2, so the COUNTIF is 2 rather than
the usual 1.

Then
=INDEX($A$1:$A$5,MATCH(1,$D$1:$D$5,0)) gives Mice,
=INDEX($A$1:$A$5,MATCH(2,$D$1:$D$5,0)) give Cats
=INDEX($A$1:$A$5,MATCH(3,$D$1:$D$5,0)) give Birds

 

[Darin Kramer]

Andrew - absolutely Brilliant!!! Thank you so very very much! works like
a charm!!!!!!!!!!!!!!!!


*** Sent via Developersdex http://www.developersdex.com ***