• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to conditionally sort values

N

NoSole

New Member
I've successfully created sheets that can sort values, but I'm absolutely stumped when I add another layer to hierarchy.


Here's the deal: I'm running an obstacle event and, simply put, I need the scoresheet to sort for the fastest times. The tricky part, though, is that the times are scratch based. So instead of competitors incurring a time penalty for each scratch, athletes' times will only be compared with other athletes with the same number of scratches. Specifically speaking, all competitors with 0 scratches will be scored first, then competitors with 1 scratch will fall in behind them, then 2 scratches fall in behind the 1-scratchers, and so on. For example, three athletes score 0 scratches with times of 22, 26, and 32 secs. Then two athletes score 1 scratch with times of 17 and 19 secs. Despite the fact that the 1-scratchers had the faster times, they would still be relegated to 4th and 5th place overall.


Here's is the current scoresheet with arbitrary values entered. You'll see that it's the fastest of two attempts. The green highlighted area is where values are entered manually, while the columns directly the left simply choose the run with the least number of scratches and the time related the that run with the fewest scratches: http://www.sendspace.com/file/f8llaj
 
No, no sole, you do the times first. This will put all times in order. Then you sort by scratches, which will group the scratches with the times still in order. You sort them by "least important" to "most important" to put the most important as the main grouping. Try it my way and you will see.
 
Hmm, OK, I see your point. To clarify though, I'm not just using the 'Data > Sort' option in Excel. I'm writing the functions so that everything sorts in real time. So what I need help with is choosing the right combinations of functions so it automatically sorts as I enter the data into the sheet.
 
NoSole


Have a look at: https://www.dropbox.com/s/7bph56rnloanuwr/O-Course-1_Hui.xlsm

I have added some VBA code which will resort the data everytime the sheet changes

You can add data and delete rows etc

It works as long as there are values in Columns A, B & C

I had to rearrange the top 3 rows slightly
 
You must log in or register to reply here.
Back
Top