Who appears in only one of two lists? Intersection, difference and union

When two lists do not match, the hard part is not the comparison, it is deciding what you are comparing. Last year my company ran a health check and admin sent me two txt files saved out of spreadsheets: one sign-up sheet from a group thread, 87 lines, and one roster exported from HR, 92 lines. The two differed by 5 people, and working out who did not sign up and who is no longer on the roster by eye would have taken the whole afternoon. The first time I split those three questions apart with an intersection and a difference, I had answers in minutes, and then a new question appeared: three of the lines in the difference were only there because of an extra space.

Get the direction of the three terms clear first

The easiest thing to confuse in set operations is the difference, because it has a direction. A - B means in A but not in B; swap A and B and you get a different group of people. I treated the sign-up sheet as A and the roster as B, so A - B was signed up but not on the roster, which means outside people or people who have left, and B - A was on the roster but not signed up, the group that actually needs chasing. Those two results are completely different, so decide which list is the baseline before you pick a direction; get the order wrong and the whole job is wasted.

The intersection A ∩ B is the entries present on both sides, the people confirmed to have signed up and to be on the roster. The union A ∪ B merges the two and keeps one copy of each entry, which is how you confirm how many people are involved in total. The union is the number most often ignored and the one that best reminded me how messy this data was: with 87 and 92 lines, the union was 95, meaning only 95 people existed while the two lists together held 179 lines.

The numbers have to agree before the job is done

When I finish, I run an arithmetic check. The advantage of set operations is that the results have fixed numerical relationships, so an error shows up immediately. For the health check the four numbers were the ones in the self-check column:

Because I ticked deduplication for the 2 duplicate lines in A, the number who signed up is really 85, and the 3 entries in A - B are three names; once both sides agreed I moved on. There is a trap here: without deduplication the intersection keeps one copy on first appearance, but you assume all 87 entries in A took part in the comparison, and the length of the chase list no longer matches. So I look at three counts together: the element count of A, of B, and of the result. Text Set Operations displays all three, which is enough to prove the conclusion to myself. The union has a side effect I care about: merging two lists from the same source removes duplicates at the same time, one step fewer than merging and then deduplicating.

Fake differences are what eat the time

With the 8 entries from B - A in hand, I found that 3 of them had clearly signed up. The reason is simple: they were differences at the text level.

The first kind is trailing spaces. When a spreadsheet is saved as txt, an extra space typed into a cell is common, and 'Li Lei ' and 'Li Lei' are two different things to a string comparison. My approach is to trim leading and trailing spaces on both sides before comparing, and there is an option for exactly that. This changes the result count directly, so I always note whether I turned it on: on gives 8 differences, off might give 11, and if you cannot say which you used, the comparison is worthless for reporting.

The second kind is case. Email addresses and English names are the usual victims: LiLei@corp.com and lilei@corp.com are the same person, but a case-sensitive comparison reports one on each side. My method: first pass ignore case and treat the result as candidates for real differences; second pass restore case sensitivity and see how many entries were flattened, and those are the ones written inconsistently, which need fixing rather than merging.

The third kind is invisible characters: tabs, full-width spaces, and the marker some files carry when pasted in from another encoding. You cannot see them, but they make a line that looks identical compare as different. When that happens I run the text through a cleaning tool to normalise whitespace first, then do the set operation.

There is another kind that is not a fake difference but a real problem: duplicate names. Both lists contain a Zhang Wei, and the intersection treats them as one person. If employee numbers are listed too, those two rows are genuinely different and the comparison produces two extra differences. For any comparison based on names I insist on a unique column, employee number or email, or set operations cannot help.

It is not the same as a line-by-line diff

One related thing that gets confused: set operations ignore order and the position of duplicates, they only ask whether this entry is in that list. So they cannot answer which line changed between two versions, or what line 5 became. For that kind of comparison, where line order matters and you need to see where the change is, I use Text Diff, which aligns the two sides by line and shows the red and green differences. And if all I want is to drop entries where the same person appears twice and sort by surname, Text Sort and Dedupe is more direct.

Can several lists be compared at once?

The tool takes two lists, so with three or more I work in steps: intersect the first two, treat the result as the new A, then intersect that with the third; to find entries that are in any list but not the others, compute the pairwise differences and look at the overlap. Keep all three results, or you cannot say what the baseline was at step two or three. Beyond a few lists I stop relying on chaining: at that point I concatenate every list into one column, add a source column, and go back to a spreadsheet.

Questions that come up often

Q: Why does my difference have several more entries than expected?

Nine times out of ten it is a formatting difference, not a data difference. Check in order: whether leading and trailing spaces were trimmed, whether case was ignored, whether there are tabs or full-width spaces, whether blank lines took part. Normalise those and compare again; the entries that disappear were caused by formatting.

Q: Should I look at A minus B or B minus A?

It depends on who you are acting on. To chase the people who have not done the thing, take the full set minus the done set, which is the B - A direction; to find gaps or outside data, take the done set minus the full set. Do not memorise left minus right, memorise which one is the baseline.

Q: Do duplicate entries skew the result?

Yes. Duplicates make the counts unexplainable, because you cannot tell whether a difference is an extra person or an extra line. I usually turn deduplication on and separately record the raw line count and the deduplicated count; the gap between them is itself a data quality indicator.

Q: What is the union for, why not just concatenate the two lists?

Concatenating gives you lines; the union gives you unique entries. For reporting those numbers can differ wildly: for the health check it was 179 lines against 95 entries. If you need a complete list of people, use the union; if you need to keep every raw record, only then concatenate.

Q: What if a list has brackets, suffixes and other noise?

Clean both sides the same way before comparing, or the formats differ and the intersection comes out absurdly small. Consistency matters: clean A but not B and people who are the same get washed into different entries.

Back to those two lists five people apart. In the end 8 people were chased, 5 of whom genuinely had not signed up and 3 of whom were fake differences caused by spaces. That left me with two rules: confirm the baseline and direction before computing a difference, and normalise the text first, spaces, case and invisible characters, then verify the counts against each other afterwards. Do both and the result can go out as is; skip one and you lose another afternoon.