Trying to write "2nd highest" score in addition to MaxScore.

  • It would be so cool for a "2nd highest" designation or key in vba, but I can't find one.


    If each cell was just a number, I'd write each to a new Sheet, sort and get the value in A2.


    But because the cell format is like this 89(SP) I'm not clear how. Of course Excel won't recognize this format as a number.


  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Hello,


    Not sure to understand your question ... But there are many functions to extract numbers from strings ...left(), right(), mid(), replace(), instr(), etc ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Thanks! let me elaborate a bit.


    The above code looks in 4 columns and uses MaxScore to write the largest to column 35.


    The values look like this, for example. 85(AN), 129(AF), 98(AP), 23(SP)


    So in the above case it adds '129(AF)' to column 35. I'm trying to figure out how to write '98(AP)' to column 36 say.


    I THINK RceNo value is already just the number. Then MaxScore just grabs the largest one. I keep hoping for a 2nd largest key. lol

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Hello,


    For your information, the function Large(YourRange,2) would retrieve the second largeest ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    So let's say I do use a function instead. Does anyone know how to deal with this part? '(AN)'


    I swear this worked before! But it only returned just the number. Assuming something like this in A1:A4;
    [TABLE="width: 288"]

    [tr]


    [TD="class: xl64, width: 72"]72(AP)
    [/TD]
    [TD="class: xl64, width: 72"]16(SP)[/TD]
    [TD="class: xl63, width: 72"]84(AN)[/TD]
    [TD="class: xl65, width: 72"]60(AF)
    [/TD]

    [/tr]


    [/TABLE]


    =MAX(VALUE(LEFT(A1:D1,FIND("(",A1:D1)-1)))
    I'm doing something wrong, it's returning #Value


    I need to display the whole cell contents. Of course I wasn't then even able to try Large(YourRange,2). But this is cool. It does work, returning 2nd largest number!, when only numbers are displayed.

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Hello,


    Below is your Array Formula (instead of Enter ...use simultaneously the three keys Ctrl + Shift + Enter)


    Code
    =LARGE(VALUE(LEFT(A1:D1,FIND("(",A1:D1)-1)),2)


    Obviously replacing 2 by 1 would produce the Max ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Quote from wittonline;790238

    Humm...very interesting.


    I'm trying to incorporate it into a macro though. I've never been able to properly use functions in a macro.


    If it's a function you would use on the actual spreadsheet you usually need to inform the VB that's what you intend to use, for some functions on the spreadsheet there is sometimes a VBA equivalent, but it can also somehow be subtly different from the sheet version.


    To instruct the VB that you are using the worksheet version of a function prefix it with:


    Code
    Application.WorksheetFunction.


    So for the Large example given above it would be


    Code
    VariableToPassValueInto = Application.WorksheetFunction.Large(YourRange, 2)
  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Very cool! Thank you!


    Now if I can only display the rest of the 2nd largest result. From my example it would be: '72(AP)'



  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Thank you! But even continuing from here in a live macro like my example earlier is above my pay grade. :)


  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Quote from wittonline;790368

    Very cool! Thank you!


    Now if I can only display the rest of the 2nd largest result. From my example it would be: '72(AP)'


    What does "the rest of the 2nd largest result" ... mean ...?


    With your example '72(AP)', do you mean 'AP' or '(AP)' or ... something else ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    The cell values are like this say...


    85(AN) 129(AF) 98(AP) 23(SP)


    Therefore in this case the 2nd largest would be 98(AP) that I would want to write to another column. So far it's only writing 98. :)


    Quote from Carim;790386

    What does "the rest of the 2nd largest result" ... mean ...?


    With your example '72(AP)', do you mean 'AP' or '(AP)' or ... something else ???

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Hello,


    You can test following array formula ...


    Code
    =INDEX(A1:D1,MATCH(LARGE(VALUE(LEFT(A1:D1,FIND("(",A1:D1)-1)),2)&"*",A1:D1,0))


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Fantastic! Wow thank you!


    I swear in a way functions are harder for me to figure out than macros!


  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Quote from wittonline;790482

    Fantastic! Wow thank you!


    I swear in a way functions are harder for me to figure out than macros!


    Glad the formula is helping you out .... !!!


    Thanks ... for your thanks ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    You're welcome!


    I have one last request along the same lines! I have NO idea what I'm doing with Formulas! :)


    I'm now trying to find the largest and 2nd largest value, adding two columns (4 total) rather than just comparing the 4 columns.


    Don't laugh. :) I tried to do a SUM range like this..."A1+D1:E1+H1" (with and without quotes)


    Code
    =INDEX("A1+E1:D1+H1",MATCH(LARGE(VALUE(LEFT("A1+E1:D1+H1",FIND("(","A1+E1:D1+H1")-1)),1)&"*","A1+E1:D1+H1",0))


    As you can see, I'm trying to add column A1+E1, B1+F1, C1+G1, D1+H1 then display the largest or 2nd largest total without having to create 4 new columns!


    [TABLE="width: 576"]

    [tr]


    [TD="class: xl63, width: 72"]97(AN)[/TD]
    [TD="class: xl63, width: 72"]40(AF)[/TD]
    [TD="class: xl63, width: 72"]102(SP)[/TD]
    [TD="class: xl63, width: 72"]65(AP)[/TD]
    [TD="class: xl63, width: 72"]27(AN)[/TD]
    [TD="class: xl63, width: 72"]30(AF)[/TD]
    [TD="class: xl63, width: 72"]45(SP)[/TD]
    [TD="class: xl63, width: 72"]65(AP)
    [/TD]

    [/tr]


    [/TABLE]


    In this example:
    Largest: 147(SP)
    2nd Largest: 130(AP)


    Quote from Carim;790496

    Glad the formula is helping you out .... !!!


    Thanks ... for your thanks ... :smile:

  • Re: Trying to write "2nd highest" score in addition to MaxScore.


    Probably a UDF..


    Hit Alt+F11 > goto Insert > Module and paste the following code there. Then hit Alt+Q to close the VBE window.


    Now in the worksheet, use the formula as MAXNTH(A1:H1,2)


    [vb]Option Explicit


    Function MAXNTH(NumRange As Range, Optional Nth As Long = 1)

    Dim k, kk, i As Long, c As Long, r As Long

    c = NumRange.Columns.Count
    r = NumRange.Rows.Count

    If c > 1 And r > 1 Then
    MAXNTH = CVErr(xlErrNum)
    Exit Function
    End If


    If c > 1 Then
    If Nth > c Then
    MAXNTH = CVErr(xlErrNum)
    Exit Function
    End If
    k = NumRange.Parent.Evaluate("transpose(transpose(" & NumRange.Address & "))")
    ElseIf r > 1 Then
    If Nth > r Then
    MAXNTH = CVErr(xlErrNum)
    Exit Function
    End If
    k = NumRange.Parent.Evaluate("transpose(" & NumRange.Address & ")")
    End If

    With CreateObject("scripting.dictionary") 'used dictionary object to get the unique items
    .comparemode = 1 'not case sensitive
    For i = 1 To UBound(k)
    kk = Split(Replace(k(i), ")", vbNullString), "(") 'split the item with delimiter '(' also replace ')' with null
    .Item(Trim(kk(1))) = .Item(Trim(kk(1))) + CDbl(kk(0)) 'add the value for each item
    Next
    c = .Count
    If c Then
    k = .keys: kk = .items 'get the keys and items in respective array variables
    If Nth > c Then
    MAXNTH = CVErr(xlErrNum)
    Exit Function
    End If
    c = Application.Large(kk, Nth) 'get the Nth largest
    r = Application.Match(c, kk, 0) 'match the position of Nth in the array

    MAXNTH = c & "(" & k(r - 1) & ")" 'using the position get the Item
    End If
    End With

    End Function[/vb]

Participate now!

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