Moving lines of a 2D array to a second 2D array based on a condition

[DonEB]

I have a 2D array (called arPriority) which was defined as Variant and populated with values from Sheets("AllWeeks").Range("L43:M72").Value2. So it is only 2 columns and max of 30 rows. This array contains Player #'s in column 1 and Priority numbers ranging from 1 to 99 in column 2.

Once arPriority is established, I examine it to determine which Player(s) have the highest priority (i.e., usually a 1) assigned to them. This is accomplished by

Priority = Evaluate("MIN(AllWeeks!M43:M72)")

However, this is where I run into problems.

For i = 1 To UBound(arPriority)
If arPriority(i, 2) = Priority Then 'NOTE: Works up to here; this helps me determine if a player has been assigned the highest priority

????? I don't know how to take the corresponding player (i.e., arPriority(i,1)) and place them into a separate array. When attempting to strip out the desired data and place it into a separate array, I run into Subscript out of range issue and I'm not sure how to resolve. My internet research has not provided me any solution to my problem.

End If
Next i

My reason for wanting to place these values into a separate array is to help limit the number of times I need to loop thru all the potential player values just to find the ones that have the high priority.
Example: If I calculated things correctly, if I have 8 players, two team of 4 that results in a total of 70 possible combinations. The program would have to loop thru 70 times. If I have 12 players, three teams of 4 that would result in 495 combinations for the first court and 70 combinations for the second resulting in over 34,000 loops (495 x 70). So, I'm thinking the smaller the subset the quicker the loop will run and thus be more efficient.

However, I would also be open minded about any other solution someone might have.

Any guidance would be appreciated.
Thank you,
Don

 

[Peter_SSs]

I'm not exactly sure what you are trying to do, but see if this is any use to get you headed in the right direction.

Sub Test_v1()
  Dim arPriority As Variant, arTop As Variant
  Dim i As Long, k As Long, Priority As Long
  
  With Worksheets("AllWeeks")
    arPriority = .Range("L43:M72").Value2
    ReDim arTop(1 To UBound(arPriority), 1 To 2)
    Priority = Evaluate("MIN(AllWeeks!M43:M72)")
    For i = 1 To UBound(arPriority)
      If arPriority(i, 2) = Priority Then
        k = k + 1
        arTop(k, 1) = arPriority(i, 1)
        arTop(k, 2) = arPriority(i, 2)
      End If
    Next i
    .Range("P43").Resize(k, 2).Value = arTop
  End With

My sample data is shown in in L:M below and the code above has produced the values in P:Q
If that is nothing like what you are after then please give us some sample data and expected results (preferably with XL2BB so that we can easily copy to test with)

DonEB.xlsm
	L	M	N	O	P	Q
41
42	Player #	Priority #
43	Player #1	4			Player #6	1
44	Player #2	3			Player #7	1
45	Player #3	3			Player #12	1
46	Player #4	4
47	Player #5	4
48	Player #6	1
49	Player #7	1
50	Player #8	4
51	Player #9	4
52	Player #10	2
53	Player #11	4
54	Player #12	1
55	Player #13	3
AllWeeks

 

[DonEB]

Thank you! This worked perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.