Formula for matching and moving contents

  • I Have a list of names that are a result of a golf competition, they are divided into 2 columns, one their Gross score & their Nett score.


    What I want to do is determine the winner of the Nett competition, a person can only claim one trophy so this is where it gets complicated.


    In list 1 the Gross winner could also be the Nett winner but as they can only claim 1 trophy they will take the Gross Trophy.


    this is what should happen, IF H8=E8,E9,H8 and so on so that if the same name appears in the same position on both lists then the next name is elevated.


    Hope that someone can understand this and help? :?

  • Re: Formula for matching and moving contents


    I think I understand basically what you want. See attached.


    The main difficulty is in not giving a rank in the Nett competition to the winner of the Gross competition and to not leave a hole where their rank would be. To accomplish, I used this formula:


    Code
    From cell F2
    
    
    =IF(D2=1,"",IF(RANK(E2,E$2:E$10)>MATCH(MAX(C$2:C$10),C$2:C$10,0),RANK(E2,E$2:E$10)-1,RANK(E2,E$2:E$10)))


    First part doesn't give a Nett rank to the winner of the Gross.


    Rest of formula checks to see what Nett ranking the winner of the Gross competion would have had. If the ranking of the current cell is greater than the ranking of the Gross winner, subtract 1 to close the hole.


    Makes sense?


    Alex.

  • Re: Formula for matching and moving contents


    [QUOTE=Joebbshop]I think I understand basically what you want. See attached.


    The main difficulty is in not giving a rank in the Nett competition to the winner of the Gross competition and to not leave a hole where their rank would be. To accomplish, I used this formula:


    Code
    From cell F2
    
    
    =IF(D2=1,"",IF(RANK(E2,E$2:E$10)>MATCH(MAX(C$2:C$10),C$2:C$10,0),RANK(E2,E$2:E$10)-1,RANK(E2,E$2:E$10)))


    First part doesn't give a Nett rank to the winner of the Gross.


    Rest of formula checks to see what Nett ranking the winner of the Gross competion would have had. If the ranking of the current cell is greater than the ranking of the Gross winner, subtract 1 to close the hole.


    Makes sense?


    Alex.

  • Re: Formula for matching and moving contents


    Your formula works fine in the ranking and showing the best result for each colum, but its not quite what I have in mind, have included another workbook with some formula in how I think it should work.


    But because I dont know how to include more than 2 arguments in these formulas this is where I am getting stuck.


    Hope this helps explain things a little better. :?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!