+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    office MAC 2011
    Posts
    51

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

    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
    Attached Images Attached Images

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

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

    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.
    Glenn



  3. #3
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    office MAC 2011
    Posts
    51

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

    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
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,596

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

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    09-05-2016
    Location
    Vancouver, Canada
    MS-Off Ver
    office MAC 2011
    Posts
    51

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

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

    Chris

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,596

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sort Rows and copy sort results to new worksheet?
    By Apache_sim in forum Excel General
    Replies: 0
    Last Post: 06-30-2015, 03:17 AM
  2. [SOLVED] I have a sort macro. How to add script to preselect rows to sort based on column value?
    By Jasonhouse in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-09-2014, 02:25 AM
  3. sort entire row based on formula result in Column D
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2013, 12:59 AM
  4. Need help creating a formula that will sort and populate based off certain selections
    By stephanieisanerd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 05:37 PM
  5. Replies: 4
    Last Post: 03-14-2013, 09:14 PM
  6. Replies: 4
    Last Post: 11-20-2012, 06:00 PM
  7. Replies: 5
    Last Post: 01-13-2012, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1