title: Excel 2 column comparison: duplicate in one column, but unique as pair
body: <p>I'm trying to compare values across two columns (Product and Location) in Excel to highlight duplicates, but I can't figure out the logical test. I'm looking for rows that match three criteria:</p>
  
  <ol>
  <li>Product value is duplicate</li>
  <li>Product value is not blank</li>
  <li>Duplicate 'Product' values occur with different 'Location' values</li>
  </ol>
  
  <p><s>(Products with the same value in the same location are assumed to be distinct for a value I don't have, but products with the same name at different locations may be duplicate).</s>
  Edit: I want the formula to evaluate to true only if the same Product occurs in more than one Location.</p>
  
  <p>I can figure out the first two conditions by themselves, but can't figure out how to incorporate the third. I've sorted the table so that the duplicate values should appear next to each other, but there are too many to check by eye. </p>
  
  <p>I'm currently using this logical test for conditional formatting using the first two criteria. 'Product' occurs in column C, 'Location' occurs in column I.</p>
  
  <pre><code>=AND(C1&lt;&gt;"",COUNTIF($C:$C,C1)&gt;1)
  </code></pre>