LARGE function handling dupicates returning an index

Hi Experts,

I have been struggling for a while now trying to solve a problem with standard Excel functions (LARGE, optionally with array functions)...so far no luck. I hope using VBA can solve this and also make it faster.

Data tables:
(@A15)
Freq Power
4 -40
10 -5
100 -3
180 -25
250 -5
270 -31
290 -27
295 -6
298 -9
300 -30

I need to derive the 4 largest powers and from this table
(@E15)
Rank Power Freq
1 -3 100
2 -5 10
3 -5 10 should be 250
4 -6 295

(@F16 and copied down) =LARGE($B$16:$B$25,E16)
(@G16 and copied down) =INDEX($A$16:$A$25,MATCH(F16,$B$16:$B$25,0))

This leads to 2 problems:
1) LARGE correctly returns duplicates (power of -5), but trying to find the corresponding freq based on only a value is not possible (the second instance should be 250 and not 10)
2) In the real situation I have much larger data tables and many more of them to analyze and this solution takes too long at the moment

I was looking for a solution in VBA (I have almost no experience in VBA programming) to create a LARGE-like function that returns the index of an array instead of the actual value. Then the standard excel formulas can easily take over.

Hope that someone can help

Hi mikerickson,

That's a fast response! Thanks for the effort. Reviewing your solution I see that what you are doing is making the data unique by adding a row dependent fraction so that the index can be uniquely found. In my case I have no control on the data and they are actually (precision limited) floats, only simplified to integers for this example. This approach is a very creative one though, but seems to shift the duplicate problem to other values. If the second 3 is 2.9 you will get:

LARGE(A1:A5+ROW(A1:A5)/10, 2) = 3.3
LARGE(A1:A5+ROW(A1:A5)/10, 3) = 3.3

Still thanks for the effort and very fast response!

Hi Richard,

You're right. I will upload a workbook copy. Thanks for the suggestion.

Here a copy of the problem in a workbook (and a simplified version of the problem in another tab). I hope this helps to make suggestions to help me out. Many thanks!

Check attachment to see if the formulas in Sheet "RealProblem" F33:F41 solve your problem.

Hi WHER,

Great! Your solution indeed works! It checks if the value was already presented and if true it is skipped. This indeed effectively finds the corresponding frequencies of the duplicate power values. I have not yet included this in my large sheet with all the data, but I am afraid that it will be slower due to extra nested functions. I still hope that a solution with a VBA function is possible.

Nevertheless it now functionally works and I can proceed, thanks a lot!

A UDF version of the above:

Hi WHER,

First of all "A happy newyear!" And secondly; thank you very much for your UDF solution! Not at all the solution direction I was thinking of myself, but it is working perfectly