Ignore Duplicates and Create New List of Unique Values in Excel

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:

Column A   Column B  
a          a
a          b
b          c
c
c

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 sheet2!A1 style format.

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.

Totero's answer is correct. The link is also very helpful.

Basically the formula you need is:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

Then press ctrl+shift+enter (or it will not work using a array formula).

Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted in cell B2 (Not B1 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.

There is a good guide of how to do this here.

Basically Something similar to:

=INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0))

In my case the excel was frozen when using the formula of

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

because there was many rows (10000). So I did in another way which I show below.

I have copied my original list to a second column and then with the function of Excel "remove duplicates" I could find the list of unique values.

Copied from Microsoft Office Website:

Select all the rows, including the column headers, in the list 

you want to filter.

Click the top left cell of the range, and then drag to the bottom right cell.

On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.

The filtered list is displayed and the duplicate rows are hidden.

On the Edit menu, click Office Clipboard.

The Clipboard task pane is displayed.

Make sure the filtered list is still selected, and then click Copy Copy button.

The filtered list is highlighted with bounding outlines and the selection appears as an > > item at the top of the Clipboard.

On the Data menu, point to Filter, and then click Show All.

The original list is re-displayed.

Press the DELETE key.

The original list is deleted.

In the Clipboard, click on the filtered list item.

The filtered list appears in the same location as the original list.

Source: Microsoft Office Website (link removed, cause dead)