Create a qualification list based on final rankings from season

I'm trying to create a separate sheet that will self populate based on the results from the season. I am trying to cut down on having to manually go through the final rankings to come up with the final event qualification list. The selection method for creating the final qualification list is to start with the best ranked Slalom competitor (i.e. David Ketterer, 1), the next step is to go the Giant Slalom list and take the best ranked competitor from that list (i.e. Sam Dupratt, 2). You then go back to the Slalom List and you simply go back and forth until you fill out the list. If you come across a competitor who is already qualified you skip that said competitor and go back to the other list (you don't skip and go down the same list, you must always go back and forth even in the instance of an athlete already being qualified). I also need their "Sd" ranking to be listed in their respective disciplines (i.e. David Ketterer, SL Overall 1 GS Overall 2).

I've attached an example of what I am working on. I've manually done the Qualification Ranking so you can visually see what I'm trying to do, but hopefully in the future can cut down on the time it takes to go through the lists by making the "Qualification Ranking" automatic.

Any help or ideas would be appreciated!

MarvinP,

The VBA code you did does seem to work upon some initial tests. Thank you very much for the help as I didn't think about VBA. I will take a closer look at the code and see how I can possibly clean it up slightly. Very nice work, thank you!