Forum Discussion
Using UNIQUE function to remove duplicate values
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 entrie...
How can we get Unique to reference values from one table to another?
sorry I can get what you mean?
table?
i saw in the attachment
you have obtain the unique values
sorry I can get what you mean?
table?
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.
