Day 108 · Thursday, 19 November 2026

From the archive

Advanced

Keep the First Sign-Up and Flag the Repeats with TRIM, LOWER and a Growing COUNTIF

A list merged from two sources always has people in it twice, and rarely typed the same way twice. Cleaning the text first makes the copies match, and a COUNTIF whose range grows as it is copied down counts only the rows above, so the first sign-up is kept and only the repeats are flagged.

Task:

You are organising a charity fun run, and the sign-ups from the paper forms and the website have been pasted into one list in A2:B10. Before the reminder email goes out, you want everyone on it once. In C2, turn the email in B2 into a clean one: no spaces at either end and all lower case. Copy it down to C10. Then in D2, show "Keep" if that clean email has not appeared in any row above it, and "Remove" if it has, so the first sign-up for each person is kept. Copy that down to D10 too.

The daily challenge is part of Pro

A new exercise every morning, plus every day that came before it — a back catalogue that grows by one a day, whether or not you were here for it.

Every day so far

August 2026

September 2026

October 2026

November 2026

21
SolvedOpenProLatest

Recent days

The last 14 days. Today's challenge is free while your streak is alive; Pro opens every past day too.