Using Match & Large to rank values.

Well, as the title suggests, I'm using a Large function embedded in a Match function to a ranking.

The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once. Does anyone have an idea on how to rectify this?

Please Login or Register  to view this content.

Thanks!

Solution may be here using LARGE and FREQUENCY further down the page

http://en.allexperts.com/q/Excel-105...p-5-Values.htm

You could add an insignificant random number to all the sales figures, which would force excel to treat them as different?

Hmm, whereas that could be a solution, my problem is that I can't (prefer not to) alter the data on the table at all. The table is a source for several pivot tables through out the workbook.

I made an alteration to get a step closer to a solution:

Please Login or Register  to view this content.

Hmm is the part I'm working on now. If there are more than one of the values, then do ~Something.

I believe I'm going to make a second "Match" loop that starts at the row below the first result on the multiple matches. I just thought of this as I typed this post out, lol. I'll try it out and let everyone know how it goes.

Well, I solved it on accident.

As I was typing out the above post, I had an epiphany of sorts. I threw a couple of counters, If/Thens, and Loops in my code and came out with the following.


In case you have a hard time following my variables, here is the breakdown.

Botow = Bottom Row of Data
Resarge = Results of Large
Poe = Partial Row Number
Occal = Occurrence Total
Occount = Occurrence Count
Loe = Last Partial Row Number
Margest = Match & Largest

Please Login or Register  to view this content.