Chapter 17 · 17.13
Comparing two lists: who is in one list but not in the other?
The membership list against the payments, last year's customers against this year's, the stock list against the order: you want to know who is in one list and not in the other. Finding and removing duplicate customers finds duplicates within a single list; here you compare two lists with each other.
As an example, we use Bakkerij De Korenaar with two worksheets: Klanten2025 with last year's email addresses in A2:A8, and Klanten2026 with this year's in A2:A7.
Method 1: a helper column with gone or stays
On the Klanten2025 worksheet, type in B2:
=IF(COUNTIF(Klanten2026!$A$2:$A$7,A2)=0,"gone","stays")
Drag the formula down to the last customer. COUNTIF counts how often the address appears in the 2026 list. If that is 0, the customer has gone. You can then filter or sort on column B (see Sort and filter).
The dollar signs are important: without them, the range in the other list shifts as you drag the formula down, and the top addresses drop out at the bottom.
Method 2: a list with only the differences
On a separate worksheet, FILTER creates a list of all departed customers in one go:
=FILTER(Klanten2025!A2:A8,COUNTIF(Klanten2026!A2:A7,Klanten2025!A2:A8)=0,"nobody left")
Here COUNTIF gets a whole list as its second argument and counts for every 2025 address at once. FILTER keeps the addresses for which the result is 0. You find the new customers by swapping the lists:
=FILTER(Klanten2026!A2:A7,COUNTIF(Klanten2025!A2:A8,Klanten2026!A2:A7)=0,"nobody new")

To get everyone who appears in only one of the two lists, new or departed, use:
=UNIQUE(VSTACK(Klanten2025!A2:A8,Klanten2026!A2:A7),,1)
VSTACK stacks the two lists on top of each other. UNIQUE with a 1 as its third argument keeps only the values that appear exactly once. If an address already appears twice within one list, make that list unique first: =UNIQUE(VSTACK(UNIQUE(Klanten2025!A2:A8),UNIQUE(Klanten2026!A2:A7)),,1).
Method 3: colours instead of lists
- On the Klanten2025 worksheet, select the addresses, A2:A8.
- On the Home tab, choose Conditional formatting, New rule, type Formula.
- Type =COUNTIF(Klanten2026!$A$2:$A$7,$A2)=0, choose a fill colour and click OK.
A reference to another worksheet in a formatting rule simply works in Euro-Office; in older versions of Excel you had to use a defined name for that.

Pitfalls
- Spaces: "info@dekorenaar.nl " with a space at the end is a different address as far as the spreadsheet is concerned. Clean up spaces first with TRIM in a helper column.
- Empty cells in a range also count as a value. So only include the rows with data, or filter out empty cells with <>"".
- Numbers versus text: if you compare customer numbers and the number is stored as text in one list (aligned left in the cell) and as a number in the other, they won't match. See VLOOKUP returns #N/A: here's how to fix it.
- As the third argument of UNIQUE, type TRUE, TRUE() or 1; Euro-Office does not recognise other spellings (see Copying formulas from Excel: English function names and the comma).
