title: Ignore Duplicates and Create New List of Unique Values in different Sheet in Excel
body: <p>I am trying to do exactly what was asked in this thread</p>
  
  <p><a href="https://stackoverflow.com/questions/13307927/ignore-duplicates-and-create-new-list-of-unique-values-in-excel">Ignore Duplicates and Create New List of Unique Values in Excel</a></p>
  
  <p><strong>Except it wasn't answered fully. I would like the output in a different sheet.
  The question:</strong></p>
  
  <p>"
  I have seen this question asked in various forms numerous times before, but nothing I have tried is working for me.</p>
  
  <p>I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows...</p>
  
  <pre><code>Column A   Column B  
  a          a
  a          b
  b          c
  c
  c
  </code></pre>
  
  <p>This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the <code>sheet2!A1</code> style format.</p>
  
  <p>I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A. 
  "</p>
  
  <p><strong>The best answer given is:</strong></p>
  
  <p>"
  Basically the formula you need is:</p>
  
  <pre><code>B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
  </code></pre>
  
  <p>Then press ctrl-shift-enter.</p>
  
  <p>Two important things to keep in mind here: The complete list is in cells <code>A2:A20</code>, then this formula has to be pasted in cell <code>B2</code> (Not <code>B1</code> as that will give you circular reference).
  Secondly this is an array formula, so you need to press ctrl-shift-enter or it will not work correctly.
  "</p>
  
  <p>(This is a useful link: <a href="http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/#unique" rel="nofollow noreferrer">Unique Values</a> )</p>
  
  <p><strong>Which gives me:</strong></p>
  
  <pre><code>  Column A Col B
      a   a
      a   b
      b   c
      c   d
      c   0
      c   #N/A
      c   #N/A
      b   #N/A
      b   #N/A
      a   
      d   
      d   
  </code></pre>
  
  <p>But the output is not in a different sheet.</p>
  
  <p>I have tried a few variations of: </p>
  
  <pre><code>=INDEX(List!A2:A20,MATCH(0, COUNTIF(UniqueList!A2:A20,'List'!A2:A20),0))
  </code></pre>
  
  <p>Putting $ signs in:</p>
  
  <pre><code>=INDEX(List!$A$2:$A$20,MATCH(0, COUNTIF(UniqueList!$A$2:A20,'List'!$A$2:$A$20),0))
  </code></pre>
  
  <p>Including 'press ctrl-shift-enter'.</p>
  
  <p>But I can't figure it out. </p>
  
  <p>Please help me take a column from List to UniqueList with only the unique values.</p>
  
  <p>Thanks.</p>