title: How to determine the MAX value of a sub-group in a VBA Array
body: <p><em>MY APOLOGIES: the code snipet below can induce in error that i work from a worksheet - I got the values in the code from a worksheet only to lighten the code. <strong>The VALUES are from an ADODB dataset that is then copied to an array. The values are to stay in memory and no worksheets are to be used to get the end result.</strong> so sorry not to have specified this from the start.</em></p>
  <p>I have a 2-dimensional array and I am trying to get the MAX(VALUE) for each unique IDs</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>VALUE</th>
  <th>DATA</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101</td>
  <td>10</td>
  <td>1125</td>
  </tr>
  <tr>
  <td>101</td>
  <td>8</td>
  <td>2546</td>
  </tr>
  <tr>
  <td>101</td>
  <td>11</td>
  <td>1889</td>
  </tr>
  <tr>
  <td>102</td>
  <td>5</td>
  <td>3521</td>
  </tr>
  <tr>
  <td>102</td>
  <td>10</td>
  <td>2254</td>
  </tr>
  <tr>
  <td>103</td>
  <td>11</td>
  <td>3544</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>the end result should be a finalArr with the unique IDs:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>VALUE</th>
  <th>DATA</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>101</td>
  <td>11</td>
  <td>1889</td>
  </tr>
  <tr>
  <td>102</td>
  <td>10</td>
  <td>2254</td>
  </tr>
  <tr>
  <td>103</td>
  <td>11</td>
  <td>3544</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I have so far:
  I did manage to find the MAX in a specific dimension (Value)</p>
  <pre><code>Sub MX_Value()
      Dim dataArr, iMax As Long, iCount As Long, tmpArr() As Integer, MyDim As Integer
      Dim i As Integer
  
          '*NOTE: Values from worksheet is an example only
          'in real-life the data comes from an ADODB dataset
          'so i need code that works in memory only.
  
          dataArr = ThisWorkbook.Sheets(1).[A1:C6].Value
          ReDim tmpAr(1 To UBound(dataArr))
          MyDim = 2 'Desired Dimension, 1 to 2
          For i = 1 To UBound(dataArr)
              tmpAr(i) = dataArr(i, MyDim)
          Next
          iMax = WorksheetFunction.Max(tmpAr)
          iCount = WorksheetFunction.Match(iMax, tmpAr, 0)
          MsgBox &quot;MAX value is in dataArr(&quot; &amp; iCount &amp; &quot;) - with data: &quot; &amp; dataArr(iCount, 1) &amp; &quot; - &quot; &amp; dataArr(iCount, 2) &amp; &quot; - &quot; &amp; dataArr(iCount, 3)
  End Sub
  </code></pre>
  <p>but I can't figure out how to group the individual IDs to find their MAX. The only logic I can come up with would be to:</p>
  <ol>
  <li>Get first ID, then add all rows with the same ID to a tempArr</li>
  <li>Send tempArr to a Function to get the MAX and copy the MAX row to a finalArr</li>
  <li>Go to next ID not matching the previous one and start again... [???]</li>
  </ol>
  <p><em>Note: in the code example the data is from a worksheet, but only to simplify the code. In it's real-world application, the data in the array comes from an ADODB dataset - so everything must be done in memory</em></p>
  <p>Any insights would be greatly appreciated!</p>