Using UNIQUE function to remove duplicate values

Brass Contributor

How can I automate the process of removing duplicate values from data that is derived from another table in a workbook?


Sample Data:


Taking column, A, which lists multiples of the same entries and importing only unique identifiers such as a “Tag name” into column D, using a formula.


The attached in the "SampleData-RemoveDuplicate" file works fine when no tables are involved.


When we are using tables we get a spill error; see file "SampleData-RemoveDuplicateUsingTables-SPILL"


How can we get Unique to reference values from one table to another?


Best regards


5 Replies
A dynamic array can't be placed in a table. Tables are scalar-based, 1 formula, 1 cell, 1 result. A table's natural ability to autofill formulas down a column clashes with a dynamic array's ability to spill results.
How can we get Unique to reference values from one table to another?

sorry I can get what you mean?
i saw in the attachment
you have obtain the unique values

@peiyezhu - UNIQUE works fine if the data is not in a table, fails with a Spill error if it is. See attached files to show both results. 


That is precisely what I would expect.  Since the data source is a Table, why don't you use structured references rather than risk the application working through the million plus cells of an entire column array?  The FILTER formula returns an array and cannot be used within a Table, so perform the calculation outside the table in a helper range.


If you must have the result in the destination table simply read it cell by cell from the dynamic range.


Helper range with anchor cell named "distinct"
= UNIQUE(FILTER(Table1[Application / Tag Name],Table1[Application / Tag Name]<>""))

Formula to read results from "distinct#" one at a time.
= IFERROR(@distinct#,"")


IFERROR is used to trap #VALUE! errors where the table extends further than the dynamic array.

Peter you are a genius! that worked perfectly. Thanks so much.