Remove duplicate contacts (UNIQUE)
Your marketing sheet collects signups from three forms, so the same contact shows up several times in A2:A7. The email tool needs a clean list: put a UNIQUE formula in Unique list so the first unique contact lands in C2 — the full deduplicated list then spills straight down.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
UNIQUE(array) returns the unique rows of a range and spills the result downward from the formula cell — no copy-paste, no manual Remove Duplicates. The formula cell itself always holds the first value of the spilled array. If the cells below are not empty, Excel/Sheets blocks the spill with #SPILL!, so keep the output area clear. To keep only values that appear exactly once, add the third argument: =UNIQUE(A2:A7, FALSE, TRUE).
FAQ
Why does only the first name show in C2?
C2 holds the first value of the UNIQUE result; the rest of the deduplicated list spills into the empty cells below automatically. SheetDo grades the formula cell, which is the value every case checks.
What is #SPILL! and how do I fix it?
UNIQUE needs empty cells below the formula to lay out its result. If anything blocks them, Excel and Google Sheets return #SPILL!. Clear the output area or move the formula somewhere with room to expand.
Is Remove Duplicates on the Data tab the same thing?
The Data-tab command rewrites your source range in place and never updates. A UNIQUE formula lives somewhere else, keeps the original untouched, and recalculates whenever the source data changes.
Does UNIQUE treat names that differ only by case as duplicates?
Yes. "alice wang" and "Alice Wang" count as the same value. Normalize casing first (for example with PROPER) if you need case-sensitive grouping.