How to remove duplicates with a formula

Use the UNIQUE function. In Grid by Zaweba, =UNIQUE pointed at a range returns each distinct value once, spilling the list into the cells below, and it updates by itself whenever the source data changes. If you'd rather delete the repeats from the data itself, Remove Duplicates does that in one go.

Formula or command

The two approaches answer different needs. UNIQUE leaves your original data alone and builds a clean list beside it. That's what you want for a drop-down source, a summary, or a report that should always reflect the latest entries. Remove Duplicates changes the data: it deletes repeated rows by the columns you choose, which suits a one-off tidy of an imported list.

If you're unsure, start with the formula. You can always copy its result and use Paste Special to paste values only, which gives you a fixed list while the original stays untouched.

Using UNIQUE step by step

Pick an empty cell with plenty of space below it, type =UNIQUE( and select the column that holds the repeats, then close the bracket and press Enter. The distinct values spill down, and Grid draws a blue spill outline around the result so you can see its extent.

If something is in the way, such as a value typed in a cell the list needs, the formula shows #SPILL! instead. Clear the blocking cells and the list appears. To refer to the whole result elsewhere, use the spill reference: if the formula is in E2, then E2# means every cell it returned, however many there are today.

Select two or more columns and UNIQUE returns distinct combinations rather than distinct single values. Pointing it at a name and a town column, for instance, keeps two people with the same name if they live in different places.

Making the list more useful

Dynamic array functions combine well. Wrap UNIQUE in SORT and the distinct values come back in order. Put FILTER inside it and you get the distinct values from only the rows that meet a condition, such as the customers who ordered this month. Counting the rows of a UNIQUE result tells you how many distinct entries there are.

The spilled list also makes a good source for a drop-down. Point a data validation list at the spill reference and the drop-down grows when a new value appears in the data.

Using Remove Duplicates

Select the range, choose Remove Duplicates, and tick the columns that decide whether two rows are the same. Ticking only an email column, say, removes repeat sign-ups even if the names were typed differently. Grid deletes the repeated rows, leaving one of each.

Because this changes the data, it helps to know that undo covers everything in Grid, up to 100 steps, so a mistaken removal can be put back straight away.

Why duplicates sometimes survive

Values that look identical may not be. A trailing space, a capital letter in one copy, or a number stored as text in one row will all count as different. Clean the column first with TRIM or a similar function in a helper column, and check that numbers are really numbers. If you've connected your own AI account in Grid, Clean up data can trim and tidy a column for you, with a preview before anything is written.

For a list that keeps changing, UNIQUE in Grid by Zaweba is the formula answer: one cell, always current, with nothing deleted from the data underneath.