Max

D

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 ***
 
T

Toppers

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

Toppers

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

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
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top