title: count the unique values in one column in EXCEL 2010 or R with 1 million rows
body: <p>After searching the forum, I did not find a good solution for this question. If I missed it, please tell me. </p>
  
  <p>I need to count the unique values in one column in EXCEL 2010. </p>
  
  <p>The worksheet has 1 million rows and 10 columns. All cell values are string or numbers. </p>
  
  <p>I used the solution at <a href="https://stackoverflow.com/questions/18586815/count-unique-values-in-a-column-in-excel">Count unique values in a column in Excel</a></p>
  
  <pre><code> =SUMPRODUCT((A2:A1000000&lt;&gt;"")/COUNTIF(A2:A100000,A2:A1000000&amp;""))
  </code></pre>
  
  <p>But, it runs so long time that the EXCEL is almost frozen. And, it generates 25 processes in Win 7.</p>
  
  <p>Are there more efficient ways to do it? </p>
  
  <p>Also, in the column, all values have for format of </p>
  
  <pre><code>  AX_Y
  
    here, A is a character, X is an integer, Y is an integer from 1 to 10. 
  
    For example, A5389579_10
  </code></pre>
  
  <p>I need to cut off the part after (including) undersocre. for the example, </p>
  
  <pre><code>  A5389579
  </code></pre>
  
  <p>This is what I need to count as unique values in all cells in one column.</p>
  
  <pre><code>  For example, A5389579_10
                 A1543848_6
                 A5389579_8
  </code></pre>
  
  <p>Here, the unique value has 2 after removing the part after underscore. </p>
  
  <p>How to do it in EXCEL VBA and R (if no efficient solution for EXCEL)?</p>