creating a formula to sort names from one column's results and sort them based on another

**cnak** · 05-07-2017, 09:18 PM

I am looking to create a self populating ladder to be used for a race.

The racers will go through a time trial process to determine their seeding for the heats and they will be broken down to 2 separate heats of 4. The racers will race in the heat of 4 and the top 2 from each of the first heats will move on to the Final.

So bellow is a sample of what the ladder will look like. Column B will give the racers Time Trial ranking which will determine the order the racers will be placed in the ladder. Column C is the racers name. Column D will be the result of the athletes heat 1 and heat 2. the top two riders from each heat move towards the final. so from heat 1 (Racer 1, and Racer 2) and heat 2 (Racer 6, and Racer 7) will move forward to the finals. And in column F will be where they will move too, the actual order of the list for Finals which is based on the riders TT results. So the order for finals will be....

1st - Racer 1
2nd - Racer 6
3rd - Racer 2
4th - Racer 7

What I am looking to do is create a formula that will self fill Column F when I manually fill out the ranking in Column D (in Red). So it needs to sort column F and only select the top 2 from heat 1 and 2. Then look at the four racers TT rank and put them in the proper order in the Finals based on TT.

Thanks

**Glenn Kennedy** · 05-08-2017, 03:03 AM

The easiest way to do this is by making an adjustment to the formulae in column D. However, we can't do that, 'cos a picture is useless.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**cnak** · 05-08-2017, 11:36 AM

Thanks Glenn

here is a sample of the data in a Excel file (hopefully it uploaded) it has the formula for sorting the TT data for the first set of heats.... all that data is off to the side and has a corrected list where it puts the name in the correct order for the heats. what I am trying to figure out is sorting the names in the correct order in the final heat. So based on their results in the head to head heats before.... top 2 move on and need to be sorted via their TT ranking.
once this is done the only thing I would want to enter is the rank from the heat before (I already have that entered) and when doing that it should populate the next heat

hopefully this makes it easier

thanks again

chris

**JeteMc** · 05-08-2017, 05:26 PM

This proposed solution employs a helper table in the range C20:F23, which has been hidden by selecting white as the font color.
The formula that pulls the top two riders from each heat is: =INDEX(D$7:D$10,MATCH(ROW(1:1),F$7:F$10,0))
The formula that finds the TT rank for that rider is: =INDEX(Y$2:Y$18,MATCH(VALUE(LEFT(D20,2)),Z$2:Z$18,0))
The formula that ranks among the four selected riders is: =RANK.AVG(C20,C$20:C$23,1)
The formula that populates column I is: =INDEX(D$20:D$23,MATCH(ROW(1:1),F$20:F$23,0))
Let us know if you have any questions.

**cnak** · 05-08-2017, 10:02 PM

That worked perfectly JeteMc.... thanks for the help, you saved me a tun of time trying to sort that out on my own.

Chris

**JeteMc** · 05-09-2017, 08:48 AM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

creating a formula to sort names from one column's results and sort them based on another

LinkBack

Thread Tools

Rate This Thread

Display

creating a formula to sort names from one column's results and sort them based on another

Re: creating a formula to sort names from one column's results and sort them based on anot

Re: creating a formula to sort names from one column's results and sort them based on anot

Re: creating a formula to sort names from one column's results and sort them based on anot

Re: creating a formula to sort names from one column's results and sort them based on anot

Re: creating a formula to sort names from one column's results and sort them based on anot

Thread Information

Users Browsing this Thread

Similar Threads

Sort Rows and copy sort results to new worksheet?

[SOLVED] I have a sort macro. How to add script to preselect rows to sort based on column value?

sort entire row based on formula result in Column D

Need help creating a formula that will sort and populate based off certain selections

Need formula to sort column based on same info on another worksheet, in different order

[SOLVED] Excel 2007 - How to sort results of previous sort without changing data order

Attempting to sort column based on criteria - slpit names and business names

Bookmarks

Bookmarks

Posting Permissions