In Excel 2016 Copy data from column A:A to Column B:B without duplicates

Question

I have excel 2016, so I can't use the unique function. In Column A:A, I have several names but some are duplicated. I need a formula that automatically copies the names in A:A to B:B without duplicates. I tried this:

=IFERROR(INDEX($A$1:$A$1000,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$1000)+IF($A$1:$A$1000="",1,0),0)),"")

An this results in #NAME? error or presents the number 0 I even tried the CTRL+SHIFT+ENTER, and nothing.

Thanks in advance.

Answer 1

Copy & Paste as values into column B

Highlight the entire column B and under the Data tab of the ribbon, select Remove Duplicates. This will remove any duplicate names that were copied over providing you with a unique list without using the unique function.

You can also create a pivot table from your range and drag the names column into the bottom-left box of the pane, and it will also provide a unique list that way. Combine this with a table format (CTRL + T) and it will also scale with the size of the table if you reference the name for the pivotsource.