CODEWITHSUNDEEP
excelvba
Pherdindy
Pherdindy

Reputation: 1178

How to find the top 3 largest values in a 2-dimensional array using excel VBA?

Below is the code for a one-dimensional array, but it does not seem to work for a two-dimensional array.

dim m as variant, k as long
m = array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
for k=1 to 3
    debug.print application.small(m, k)
    debug.print application.large(m, k)
next k

My issue is that I want to get the top 3 largest values for each row, i. So If there are 100 rows, there will be 300 numbers which will be retrieved from column, j.

I have tried the following variations below:

Variation 1: I know this is really wrong because I am getting the largest of each data point rather than getting the largest among all the j's in each row. It returns an error every time j>1 because perhaps there is only 1 data point per iteration.

Dim i As Long, g As Long, j As Long

ReDim Min_NDate(5, 5) As Variant

For i = 1 To 5
    For g = 1 To 5
        Min_NDate(i, g) = Rnd()
    Next g
Next i

For i = 1 To 5
    For g = 1 To 5
        For j = 1 To 2
            Debug.Print Application.Large(Min_NDate(i, g), j)
        Next j
    Next g
Next i

Variation 2: Here I get an "Subscript out of range" error due to the fact that I am using a 2-dimensional array however I am only declaring it as 1 dimensional which is Min_NDate(i).

Dim i As Long, g As Long, j As Long

ReDim Min_NDate(5, 5) As Variant

For i = 1 To 5
    For g = 1 To 5
        Min_NDate(i, g) = Rnd()
    Next g
Next i

For i = 1 To 5
    For g = 1 To 5
        For j = 1 To 2
            Debug.Print Application.Large(Min_NDate(i), j)
        Next j
    Next g
Next i

Variation 3: It also errors like in Variation 2 because there is no loop with regards to the 2nd dimension, j. Although I was hoping it would be actually realize that there are values in the 2nd dimension and do what I wanted it to do as shown in the portion to do.

Dim i As Long, g As Long, j As Long

ReDim Min_NDate(5, 5) As Variant

For i = 1 To 5
    For g = 1 To 5
        Min_NDate(i, g) = Rnd()
    Next g
Next i

For i = 1 To 5
    For j = 1 To 2
        Debug.Print Application.Large(Min_NDate(i, g), j)
    Next j
Next i

Ideally what I want to happen is this: Step 1 (This is probably my main problem because I cannot display it to be in this form before I feed it to the large function):

Min_NDate(1, for all j) = [1, 2, 3, 4, 5]

Min_NDatE(2, for all j) = [3, 5, 6, 10, 11]

Step 2:

Top 3 Largest of Min_NDate(1, for all j) = [3, 4, 5]

Top 3 Largest of Min_NDate(2, for all j) = [6, 10, 11]

Upvotes: 1

Views: 1914

Answers (2)

Pherdindy
Pherdindy

Reputation: 1178

The answer lies in the Index function just like Jeeped mentioned. There is no pre-built way to slice an array unlike in MATLAB where you can slice by typing something like Min_NDate(1, :)

The working code is shown below:

Sub test()

Dim i As Long, g As Long, j As Long

ReDim Min_NDate(1 To 2, 1 To 3) As Variant

For i = LBound(Min_NDate, 1) To UBound(Min_NDate, 1)
    For g = LBound(Min_NDate, 2) To UBound(Min_NDate, 2)
        Min_NDate(1, 1) = 100
        Min_NDate(1, 2) = 50
        Min_NDate(1, 3) = 1
        Min_NDate(2, 1) = 25
        Min_NDate(2, 2) = 10
        Min_NDate(2, 3) = 2
    Next g
Next i

For i = LBound(Min_NDate, 1) To UBound(Min_NDate, 1)
    For j = 1 To 2
        Debug.Print Application.Large(Application.WorksheetFunction.Index(Min_NDate, i, 0), j)
    Next j
Next i

End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

You are not looking at the individual elements of each array correctly. wadr, the way you construct For ... Next statements has a lot to do with it.

In any array, no matter whether it is 1-D or 2-D (or more) you should never hard-code any dimension. Always use the lbound and ubound properties of any rank¹.

When you run For i = 1 To 5 instead of determining lbounds and ubounds and complicating it with a nested For g = 1 To 5 you are skipping over the first element in the array. If you ran,

For i = lbound(Min_NDate, 1) To ubound(Min_NDate, 1)
    For g = lbound(Min_NDate, 2) To ubound(Min_NDate, 2)
         Min_NDate(i, g) = Rnd()
    next g
next i

This should fill your complete array with random numbers if the .randomize code is correct.

¹ Rank s the lateral ordinal of a 2-D array. Rank 1 can be thought as by 'row' while rank 2 would be the 'column'. A 1-D array doesn't have to concern with rank although a rank of 1 is not an error.

Upvotes: 2

Related Questions