I have a worksheet with 2 columns of numbers (column A and column B) and I want to populate a 3rd column (column C) with values that only appear ONCE in both columns. That means any value that appears in column A and does NOT appear in column B, and vice versa. I've been looking for a solution all day and can't seem to find one. I want to stress that I'm not interested in a list of unique values (sans duplicates), so Advanced Data does not help me. Column C must not contain any values found in both columns.

enter image description here

  • Can you give a small sample dataset together with your expected results? – XOR LX Oct 6 '14 at 8:44
  • Here's a screebshot of an example of what I'm looking for: tinypic.com/r/33or2gg/8. Note that April and Oscar do not appear in column C because they appear in BOTH columns A and B. – Rody K. Oct 6 '14 at 10:42
  • 1
    Thanks. In that example it just so happens that there are no duplicate entries in either of those columns. Is that always the case? – XOR LX Oct 6 '14 at 12:06
  • I'm interpreting what Rody wants as unique values across Group A and Group B COMBINED. @RodyK. - is that right? Defined that way, the sample data is correct. – Mike Honey Oct 7 '14 at 2:44

Something like this, in column C at C2 would take a little while, but work


This is an array formula, so when you type it and want to hit enter, hit ctrl shft enter. and curly {} braces should show up in the formula bar.

Drag it down column C to populate.


I would use the Power Query Add-In for this. It has Append, Group and Filter commands that should achieve what you want.

I've built a solution using your data which you can view or download - its: "Power Query demo - Extract list of values that only appear once.xlsx" in my One Drive:


First I created a brief Query that gets just the "Group B" values, renamed to "Group".

Then my main query renames "Group A" to Group, then appends the "Group B" data, Groups by "Group", and Filters to Count = 1.

Done in a couple of minutes with no complex formulas or VBA code to worry about. In fact I didn't need to use the keyboard at all ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.