Dec 13 2022 01:18 PM
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
Dec 13 2022 01:43 PM
Dec 13 2022 02:48 PM
Dec 13 2022 03:05 PM
@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.
Dec 13 2022 03:54 PM - edited Dec 13 2022 03:55 PM
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.
Dec 13 2022 04:25 PM