Use second column to break ranking ties

[swallis]

I'm hoping to find a VBA solution to breaking ties in rankings. Attached is a simplified version of what I'm trying to achieve. I need to rank the top four performers in each location, using Scor A (High is best) and breaking any ties by using Scor B (Low is best). The result in "Rank" can either stop at 4 or revise the whole list. I've searched, but most solutions are not Vba and seem to use an artificial "Count" function to break ties. If they use 2 columns they only seem to combine the columns and then rank the result. Any help will be most appreciated.

[p24leclerc]

Do you need the macro to automatically select the range for each location and do the sort?
Or can you select the range of data (like E2:G10) for location Nsw in your example and manually trigger the macro?

[swallis]

Thanks for the reply Pierre. I would prefer the macro to run without manual involvement if possible. It will be part of a much larger procedure.

[scottiex]

Why VBA?

Normally combining the columns and ranking the result with something like

Please Login or Register  to view this content.

which seems to do the job for most purposes.

but try this (copied down)

Please Login or Register  to view this content.

The 2 in the D2 and F2 represent the row you are in, and the ranges represent the ranges you are ranking.

edit:
woops I missed the only top 4 bit

here is a way to do that

Please Login or Register  to view this content.

[swallis]

Thanks for the response Scottiex. Why vba? I think it's quicker, cleaner and gives me smaller files. But primarily I just like using it. It's purely hobby for me and I'm not good at it, so there is enormous satisfaction when I get things working.

I like your middle solution because it's simpler than only the top 4 and I don't mind if all are ranked. Will now go and play with putting it into vba (using Match & Index?). If you or anyone else cares to offer further assistance I'd be grateful.

Steve

[xladept]

Hi Steve,

Here's a VBA solution:

Please Login or Register  to view this content.

[swallis]

Thanks for your solution Orrin. It works almost perfectly, with just a couple of glitches which I've highlighted on the attached worksheet. The problem is, having stared intently at it for an hour, I haven't a clue how it works! For example, your code seems not to refer to the columns used, yet obviously does. I would have no hope of translating it into my real world without a much better understanding.

Is it possible that you could go through your code for me in minute detail and explain what everything is and does? I like to try to understand code I use if I can.

I notice when the code executes it seems to re-rank everything, then only shows the top 4. I'm happy to have all new rankings shown if it is simpler - and easier for me to understand.

We are on very different time zones, so please don't be impatient if I take a while to respond to your assistance.

Thanks again for your help.

steve

[xladept]

Hi Steve,

I'll have to work on it tomorrow - the time zone thing!

[scottiex]

in that case try this tiny change

I had one concern except I note it worked regardless.

Please Login or Register  to view this content.

[xladept]

Hi Scottiex,

I don't understand your concern - n can only be 1 to 4

But thanks for saving me the trouble of commenting my code.

@ Steve - why do you think there's a glitch

[scottiex]

Well... Not the greatest comments...
but maybe it is enough for Swallis to get started.

As to the concern I had - the below scenario.

If you have a tie for 3rd in the first column you might return 1.1, 2.5, 3.2, 3.6 and 3.9.
Then when it ranks them (using SMALL) it goes down the list and enters 1..2..3..4.. but when it evaluates the last cell - its the 4th smallest so that gets 4 also even though it was the 5th smallest.

I put it in the sheet and that is the result I get.

[swallis]

Thanks Scott. That seems to work perfectly. I'm still struggling with the logic, but getting closer I think. Might be wrong, but I think that as long as "For n = 1 To 4" works in that order, there's no chance of a change in Small. I can see we add one percent of the second rank to the first, but how? Is it the & "." & that achieves this?

Anyway, thanks again for your help. I had got something working thanks to your input yesterday, but it was one location at a time.

And thanks once more to Orrin.

Steve

[scottiex]

I can see we add one percent of the second rank to the first, but how? Is it the & "." & that achieves this?

Yes that is right,
you get [rank1].[rank2] just like how you could write a formula in excel cell like this

A1&A2

or

A1&"."&A2

[swallis]

Thanks Scott.

Orrin, when I tested it on a larger subset of figures, a couple of rankings were wrong. They are highlighted in yellow on this morning's attachment. Scott's offering seems to have fixed it.

Anyway thanks to you both. Great help.

steve

[xladept]

' save time typing "worksheetfunction"
Dim WF As WorksheetFunction
Set WF = WorksheetFunction

Actually, by doing this we get the advantage of the VBE wherein the "tooltips" become invoked -not just saving the ponderous code

@ Steve you're welcome and thanks for the rep!

@ Scottiex - thanks again for the commentary and for the fix!

[StuCram]

Hi swallis,

Here is my proposed solution using a few macros to rank your data.

It works by determining the top and bottom rows of each group (separated by blank lines), inserting formulas to calculate the ranks and then changing the formula results to just values. An extra hidden column (J) is used to assist getting the combined ranks. In addition, any ties are flagged and each group is sorted.

Details are in the attached Excel file.
Reward test with Macros by Stu.xlsm

Hope this is some help.
- Stu

PS Like you, I like to make macros for such tasks and enjoyed doing this.

[xladept]

Hi Guys,

After perusing StuCram's code, I see that inserting a Zero rather than the dot seems to make all aright!
Whoops! Nevermind

See whether you like this:

Please Login or Register  to view this content.

*Now I see why Scottiex was concerned

[swallis]

StuCram & Orrin, Thanks for the further input, I've just found them. Going away for a few days, so won't get a chance to look at your offerings until next week. Talk to you then.

steve

[swallis]

StuCram, thanks for your help. I actually found your code easier to follow than Scott & Orrin's. I can't use the Sort bit (no xlSortonValues in 2003), but I prefer each group to remain in original sort order anyway. I'm going to use Scott's as I found it a tad faster and it doesn't need the extra columns. I'll keep yours though, because I'm more likely to be able to use that in a similar situation, than adapt Scott's.

Thanks again,

steve

[swallis]

Orrin, your code is still giving me aberrations. It's not important as Scott's works fine. The main difference I can see is Scott uses a "Format" function before the 2nd rank, which seems to make the second Rank a decimal point. No group will exceed 40 rows, so as far as I can see there's no reason for Scott's (and now your) concern. Rank 1.01 -v- Rank 1.40 -v- Rank 3.01 gives me the result I want. Or am I missing something?

Thanks once more for all your help.

Steve

[xladept]

Hi Steve,

I'm really curious as to the "aberrations" could you explain?

[swallis]

Hi Orrin. Attached is a file with a larger amount of data and I've done a comparison between You, Scott, Stu and my manual calculation. I suspect the aberrations have something to do with the zero scores in Scor B.

[xladept]

Hi Steve,

Aberrations all gone - I paraphrased Scott's wise solution

Please Login or Register  to view this content.

[swallis]

Thanks Orrin. Work perfectly. Now I'm spoiled for choice.

I'll mark the thread solved.

[xladept]

Hi Steve,

You're welcome!

One caution:

Scottiex's routine won't run on my machine, so, if you have trouble running Scottiex's on a newer system, you can use mine

For some reason, with my 2010 the Format code crashes

[swallis]

Hehe. I'm archaic in more ways than one. I use 2003 and don't expect to change, but never say never - I'll change to your code just in case.

[xladept]

I understand, I actually prefer 2003 but had to upgrade for some gigs that used over 100k rows