Wednesday, July 5, 2017

yogi_Sort Multiple Entries By Run Time In Descending Order

Google Spreadsheet   Post  #2200
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jul-04-2017
question by Michelle Greenwalt:!topic/docs/YVQx6erT3RE;context-place=mydiscussions
Using Index & Match on columns that contain repeat entries
Hello - 

I'm creating a workbook that will help us run our swim meets more efficiently. On the first sheet the home and visiting coaches list their swimmers and their best times. The second page then sorts those times from fastest to slowest with 4 colums - the first is the swimmer's name, second their time, third is the list in ascending order from fastest to slowest (using SMALL) and then the last column matches the time in column 3 to the name in column 1 and shows the name (using INDEX & MATCH.) 

These are then pulled into a 4th sheet that creates a list for each lane for the timers to use. Currently I'm manually building the sheet, but that's another question on how to make that easier. 

The issue that I have is that some kids have duplicate times (28.8 and 28.8) and INDEX & MATCH stop at the first instance of the time and match that name to the cell. So then I have two results in the 4th column for the first swimmer who has 28.8 and zero for the second. Ideally it would show the first and then the second below it in alphabetical order if the times were the same. 

Any ideas how I can have the formula disregard the first after it has been used? Thank you!!!