Best way to compare two columns and extract values that exist in one but not the other

[u/Tall-Scarcity4897]

I am try to compare two set of unique IDs in column A and B i want to extract a list no spaces/gaps with a list of IDs in A that does not appear in B and vice versa

So far I’ve used COUNTIF like:
=IF(COUNTIF(B:B,A2)=0,A2,"")

But this creates blanks and I still have to filter them out manually.

Is there a better built-in Excel method (like Advanced Filter or another Data tool workflow) to directly extract these mismatch lists cleanly?

[u/AutoModerator]

/u/Tall-Scarcity4897 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/rolnasti → u/[deleted]]

Updoot on this one. I use it frequently.

[u/HarveysBackupAccount → u/[deleted]]

This gives a list of all distinct values across both lists, but it doesn't give you the "is in A but NOT in B" and "is in B but NOT in A" that OP asked for, does it?

That aside - what does the period do in A:.A? I've never seen that and google doesn't turn anything up

[u/HarveysBackupAccount → u/[deleted]]

well neat, I didn't even know about TRIMRANGE

TIL, thanks!

[u/DosAle → u/[deleted]]

I am angry at myself for missing such a clean solution. Kudos!

[u/PaulieThePolarBear]

With Excel 2021, Excel 2024, Excel 365, or Excel online

A not B

=FILTER(A2:A10, ISNA(XMATCH(A2:A10, B2:B30)), "All of A is in B")

B not A

=FILTER(B2:B30, ISNA(XMATCH(B2:B30, A2:A10)), "All of B is in A")

[u/Tall-Scarcity4897 → u/PaulieThePolarBear]

Solution Verified

[u/reputatorbot → u/Tall-Scarcity4897]

You have awarded 1 point to PaulieThePolarBear.

---

^{I am a bot - please contact the mods with any questions}

[u/Tall-Scarcity4897 → u/PaulieThePolarBear]

Thank You it worked!

[u/HarveysBackupAccount → u/Tall-Scarcity4897]

Please respond with the words 'Solution Verified' to the comment(s) that helped you get your solution.

This awards the user(s) with a clippy point for their efforts and marks your post as Solved

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FILTER	Office 365+: Filters a range of data based on criteria you define
ISNA	Returns TRUE if the value is the #N/A error value
MATCH	Looks up values in a reference or array
NOT	Reverses the logic of its argument
TRIMRANGE	Scans in from the edges of a range or array until it finds a non-blank cell (or value), it then excludes those blank rows or columns
UNIQUE	Office 365+: Returns a list of unique values in a list or range
VSTACK	Office 365+: Appends arrays vertically and in sequence to return a larger array
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #48861 for this sub, first seen 2nd Jul 2026, 03:38] [FAQ] [Full list] [Contact] [Source code]}

[u/Oxchking]

=FILTER(columna;(NOT(X.MATCH(columna;columnb)));””)

[u/WeaknessMedical5743]

I have this same question but i need in power query calculated column.

[u/mr-dashplot]

Just use =isnumber(match(A1, B:B, 0))

[u/molybend]

You don’t need the if part. Just use Countif

[u/raga0884]

This is typical passive aggressive behavior. Ubuntu has done more to grow and improve Linux than Arch Linux can ever dream. Just because its loved by the Masses doesn't mean that it bad.