Return 10 largest values using VBA

Isn't Sorting the range quickest?

Anyway..

Sub test()
    Dim a, i As Long, w, n As Long, msg As String
    Const myRank As Long = 10
    a = Cells(1).CurrentRegion.Value
    With CreateObject("System.Collections.SortedList")
        For i = 1 To UBound(a, 1)
            a(i, 2) = Val(a(i, 2))
            If Not .contains(a(i, 2)) Then
                .Item(a(i, 2)) = VBA.Array(a(i, 1) & " (" & a(i, 2) & ")")
            Else
                w = .Item(a(i, 2))
                ReDim Preserve w(UBound(w) + 1)
                w(UBound(w)) = a(i, 1) & " (" & a(i, 2) & ")"
                .Item(a(i, 2)) = w
            End If
        Next
        msg = "Rank" & vbTab & "Name": n = 1
        For i = .Count - 1 To 0 Step -1
            If n > myRank Then Exit For
            msg = msg & vbLf & n & vbTab & Join(.GetByIndex(i), vbLf & vbTab)
            n = n + UBound(.GetByIndex(i)) + 1
        Next
    End With
    MsgBox msg
End Sub

Hi Jindon

Thanks for your answer. I am quite new to VBA programming. When i use your code i get this outputCapture.JPG.

I want to illustrate in my msgbox the name of the team and how many wins top 10 has =) Afterwards i want top 10 away wins, etc. Could you please help me understand your code? Thanks! =)

Best Regards
Martin

You have nothing in col.A.

Upload your file rather then uploading picture.

Hi Jindon

The file is uploaded now =) Im working in the sheet "overblik" atm =)

Thanks

/ Martin

Change to

Sub test()
    Dim a, i As Long, w, n As Long, msg As String
    Const myRank As Long = 10
    a = Sheets("Overblik").Cells(1).CurrentRegion.Value
    With CreateObject("System.Collections.SortedList")
        For i = 1 To UBound(a, 1)
            a(i, 3) = Val(a(i, 3))
            If a(i, 2) <> "" Then
                If Not .Contains(a(i, 3)) Then
                    .Item(a(i, 3)) = VBA.Array(a(i, 2) & " (" & a(i, 3) & ")")
                Else
                    w = .Item(a(i, 3))
                    ReDim Preserve w(UBound(w) + 1)
                    w(UBound(w)) = a(i, 2) & " (" & a(i, 3) & ")"
                    .Item(a(i, 3)) = w
                End If
            End If
        Next
        msg = "Rank" & vbTab & "Name": n = 1
        For i = .Count - 1 To 0 Step -1
            If n > myRank Then Exit For
            msg = msg & vbLf & n & vbTab & Join(.GetByIndex(i), vbLf & vbTab)
            n = n + UBound(.GetByIndex(i)) + 1
        Next
    End With
    MsgBox msg
End Sub

Hi jindon!

Thanks alot. Works almost perfect. Only issue is that i get som random numbers: Capture.JPG

Also could you help me understand the code? Im very new to VBA and would like to understand wich code does what to be able to modify later =)

Thanks alot!

/ Martin

What and where is RANDOM NUMBERS?

To the left in my msgbox =)

They are RANK.

Do you want to have top 10 from the Total wins?

Sub test()
    Dim a, i As Long, w, n As Long, msg As String
    Const myRank As Long = 10
    a = Sheets("Overblik").Cells(1).CurrentRegion.Value
    With CreateObject("System.Collections.SortedList")
        For i = 1 To UBound(a, 1)
            a(i, 5) = Val(a(i, 5))
            If a(i, 2) <> "" Then
                .Item(a(i, 5)) = .Item(a(i, 5)) & _
                IIf(.Item(a(i, 5)) <> "", vbLf & String(2, vbTab), "") & a(i, 2)
            End If
        Next
        msg = "Rank" & vbTab & "Wins" & vbTab & "Name": n = 1
        For i = .Count - 1 To 0 Step -1
            If n > myRank Then Exit For
            msg = msg & vbLf & n & vbTab & .GetKey(i) & vbTab & .GetByIndex(i)
            n = n + UBound(Split(.GetByIndex(i), vbLf)) + 1
        Next
    End With
    MsgBox msg
End Sub

Arh perfect... i understand now... it is because it dont fill the rank in the msgbox when they are the same =)

thanks alot!

Hi Jindon =)

A quick question.. How do i modify the msgbox to only show one decimal when returning %? e.g. 31,1 % instead of 0,311234383493 ?

Thanks in advance =)

MsgBox Format$(0.311234383493, "0.0%")

Thanks =)

How to make it general? work on all values if more than one? I mean how to make 0.311.... general for the whole msgbox? =)

Thanks

I don't understand what you are asking and how is it related to your original question?

The forum allows only one question per thread.

It is related because it is related to the output of my original question?

What you showed my was to make one value into %... As you saw in my sheet i got many values.. So im asking how to make the msgbox so it applys to all instead of a single value =)

But nwm if i had to make a new thread then i must do that apperently

/ Martin

As I said, I don't understand what you are asking.

Recall the msgbox from earlier Capture.JPG

Imagine that there are decimals instead of integers... Then im asking how to make them all into % instead of e.g. 0,453232121 and not only one number from earlier 0,31.... =)

So, how is it related to RANK?

Your question is really vague.

Show me the example with your desired result in a workbook.

Capture.JPG

As you can se on the picture all the values are in decimals, i want them to be in % =)

Change

.GetKey(i)

to

format$(.GetKey(i),"0.0%")

Thank you very much =)