Ignore Duplicates and Create New List of Unique Values in Excel

Question

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.

Answer 1

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.