title: Finding (NOT deleting) duplicate values(rows) in multi-dimensional array using Excel VBA
body: <p><sup>Building off of one of my past <a href="https://stackoverflow.com/questions/35183844/how-to-apply-conditional-formatting-formula-to-large-range-faster">questions</a></sup><br><strong>What I'm looking to accomplish:</strong></p>
  
  <p>I'm looking to find and highlight duplicate Upcharges using VBA code based on multiple criteria:  </p>
  
  <ol>
  <li>Product's XID (Column A)  </li>
  <li>Upcharge Criteria 1 (Column CT)  </li>
  <li>Upcharge Criteria 2 (Column CU)  </li>
  <li>Upcharge Type (Column CV) and  </li>
  <li>Upcharge Level (Column CW)  </li>
  </ol>
  
  <p>If there is more than one instance/row in a spreadsheet that share/match ALL of these criteria then that means the Upcharge is a duplicate. As seen in my previous post linked above:</p>
  
  <p><strong>What I've tried:</strong></p>
  
  <ol>
  <li>Created a general formula (see below) that is inserted into a Helper column and copied all the way down the spreadsheet which points out which Upcharges are duplicate. This method was too resource heavy and took too long (8-10 minutes for all the formulas to calculate, but doesn't lag when filtering). Then I tried</li>
  <li>Evolved the general formula into a Conditional Formatting Formula and applied it to the Upcharge Name column via VBA code.(Takes same amount of time AND lags when filtering)</li>
  <li>I've also looked into possibly using a <code>scripting.dictionary</code>, but I'm not sure how (or if) that would work with a multi-dimensional array.</li>
  </ol>
  
  <p>Now I've finally found the method I think will be much faster,</p>
  
  <p><strong>The faster method I'm looking to use:</strong>
  Dumping the aforementioned columns into a multi-dimensional array, finding the duplicate "rows" in the array, then highlighting the corresponding spreadsheet rows. </p>
  
  <p><strong>My attempt at the faster method:</strong>
  Here's how I populate the multi-dimensional array</p>
  
  <pre><code>Sub populateArray()
      Dim arrXID() As Variant, arrUpchargeOne() As Variant, arrUpchargeTwo() As Variant, arrUpchargeType() As Variant, arrUpchargeLevel() As Variant
      Dim arrAllData() As Variant
      Dim i As Long, lrow As Long
      lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
  
      arrXID = Range("A2:A" &amp; lrow) 'amend column number
      arrUpchargeOne = Range("CT2:CT" &amp; lrow)
      arrUpchargeTwo = Range("CU2:CU" &amp; lrow)
      arrUpchargeType = Range("CV2:CV" &amp; lrow)
      arrUpchargeLevel = Range("CW2:CW" &amp; lrow)
  
      ReDim arrAllData(1 To UBound(arrXID, 1), 4) As Variant
          For i = 1 To UBound(arrXID, 1)
              arrAllData(i, 0) = arrXID(i, 1)
              arrAllData(i, 1) = arrUpchargeOne(i, 1)
              arrAllData(i, 2) = arrUpchargeTwo(i, 1)
              arrAllData(i, 3) = arrUpchargeType(i, 1)
              arrAllData(i, 4) = arrUpchargeLevel(i, 1)
          Next i
  End Sub
  </code></pre>
  
  <p>I can get the columns into the array, but I get stuck from there. I'm not sure how to go about checking for the duplicate "rows" in the array.</p>
  
  <p><strong>My questions:</strong></p>
  
  <ol>
  <li>Is there a way I can apply my formula (see below) from my first attempt in my previous post and apply it inside the array?:</li>
  <li>Or, even better, is there a faster way I can find the duplicate "rows" inside the array? </li>
  <li>Then how could I go about highlighting the Upcharge Name (CS) cell in the spreadsheet rows that correspond with the "rows" in the array that were flagged as duplicates?</li>
  </ol>
  
  <p><sub>Formula from my previous post for reference:</sub></p>
  
  <pre><code>=AND(SUMPRODUCT(($A$2:$A$" &amp; lastRow &amp; "=$A2)*($CT$2:$CT$" &amp; lastRow &amp; "=$CT2)*($CU$2:$CU$" &amp; lastRow &amp; "=$CU2)*($CV$2:$CV$" &amp; lastRow &amp; "=$CV2)*($CW$2:$CW$" &amp; lastRow &amp; "=$CW2))&gt;1,$CT2 &lt;&gt; """")"
  Returns TRUE if Upcharge is a duplicate 
  </code></pre>