Move highest ranked cells to new sheet

  • I have numerous rows on sheet1 which can all contain up to 23 different values. I am using this formula which identifies and sums the best 14 values


    Code
    =IF(ISBLANK(C5),"",IF(COUNT(D5:Z5)<14,SUM(D5:Z5),(SUMPRODUCT(LARGE(D5:Z5,ROW($1:$14))))))


    Using conditional format I can also highlight those values.


    Is there a way I can take those 14 values, using code, to a new row on Sheet 2 - thereby having only the highest values recorded on sheet 2.?


    Thank you for looking.

  • Re: Move highest ranked cells to new sheet


    not clear whether you want a vb code or a formula
    see the attched file maverick.xlsm
    the follilwing macro is already in the mdoule also.
    run it and you will get sum as message.
    THIS MACRO GIVES THE SUM OF THE 14 HIGHEST VALUES. THIS NO 14 CAN BE CHANGED BY CHANGING THE DEFINITION OF J IN THE MACRO


  • Re: Move highest ranked cells to new sheet


    I have attached the workbook in its barest form.
    Sheet 1 contains a list of teams A to Z and the weeks for play - 23 in total. The best 14 scores for each time will be highlighted using conditional formatting - I have complete all values for team A to show what I mean. The question is though how can I get just the best 14 scores for each team to appear on sheet 2 and ignore the rest??


    venkat1926 :- sorry but I don't think I made myself clear in my original post.


    The reason for wanting ONLY the 14 highest scores is those are the details that will be published.


    Thanks

  • Re: Move highest ranked cells to new sheet


    That's exactly what I wanted - I tried for ages to do it with formula but have now realised where I was going wrong. thaanks for the help....and the lesson. Cheers.

  • Re: Move highest ranked cells to new sheet


    This has worked well now for some months - but as the results are filled an error is now appearing.


    Sheet 2 should show the 14 highest scores and in some cases it does do so. A problem arises when the lowest of the 14 entries is duplicated ie when there are duplicate versions of the lowest number. When this happens it ignores one of the top 14 numbers. It can be seen in player B's score. Sheet 1 shows that correct sum of the 14 highest scores but on sheet 2 the latest score of 22 is ignored in favour of a duplicated lower value of 10.


    I have created a workaround for it - where if a value appears more than once I will add 0.001 to it, where the first record of 10 is 10 and subsequent records of 10 become 10.001, 10.002 etc. This is not ideal but it solves the problem in the short term.


    Can the formula on the second sheet be altered to ignore duplicate instances of a lowest value?


    (I have PM'd Red Rooster and have posted in this thread as PM's will not allow uploading of files - he has indicated he will look at it again).


    Maverick

Participate now!

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