How to get addresses of top 5 values in 2D table, even with duplicates?

I hope this question is in the right forum.

I have a 2D table (8 columns, 250-some rows) and I need to find the addresses of the MAX, LARGE(2), LARGE (3), etc. values for a total of 5 values. Some of the values in the 2D table are duplicates and therefore sometimes, for instance, LARGE (2) is the same as LARGE (3), which may be the same as LARGE (4), etc.

I have been using VBA code that allows me to find the address of each of these values, but I can't get around the problem of the code giving me the same address (i.e. the first occurrence) of the first occurrence of the duplicate value. For instance, LARGE (2) may be 5.00 and LARGE (3) may be 5.00, but the real addresses of LARGE (2) and LARGE (3) are C38 and G101, respectively. My VBA code will give me C38 for both answers.

Is there a way to write code so that it will give me the addresses of the five highest values, even if they may be the same integer/value, giving me the addresses top down (i.e. A1 to H1, then A2 to H2, then A3 to H3, etc.)?

Thank you.

Hi -

I suggest you upload your sample file along with your codes then we can start from there.

Regards,
Event

Here's my sample file and the VBA code (sample) I have been using.

Community Champlain Lajemmerais
7.5 2 5
13.5 7.5 3
14 0 14.5
1 0 35
1 3 6
4 0 0
77.5 1 1.5
14 3 88



Function MaxAdr(rng As Range) As String
Dim c As Range
Dim MaxNum As Double

MaxNum = Application.WorksheetFunction.Max(rng)

For Each c In rng
If c.Value = MaxNum Then
MaxAdr = c.Address
Exit Function
End If
Next c
End Function

Hi -

I don't think it make sense based on your problem description.

Regards,
Event

Use in cell like
=GetRankAddress(A1:C8,5)
Then format the cell as wrap text to show all the result.

Please Login or Register  to view this content.

Unfortunately, jindon's suggestion gives me a #NAME? error. I see that my range is defined as rng, but should I be defining something as maxRank?
And I will try to make it more understandable using the results of this reloaded sample file (made simpler).
With this table, I would be hoping for an answer of C9, C5, A8, A4, A9.


A B C
7.5 2 5
13.5 7.5 3
14 0 14.5
1 0 77.5
1 3 6
4 0 0
77.5 1 1.5
14 3 88

Sorry if there was any confusion.

You must be doing something wrong.

Thanks. Not exactly what I was looking for, but close enough, and I was able to reverse-engineer what I needed. Great ideas!