0

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

4
  • Can you give a small sample dataset together with your expected results?
    – XOR LX
    Oct 6, 2014 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, 2014 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, 2014 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, 2014 at 2:44

2 Answers 2

0

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

    =IFERROR(IFERROR(INDEX(A:A,MATCH(0,COUNTIF($C$1:C1,A:A),0)),INDEX(B:B,MATCH(0,COUNTIF($C$1:C1,B:B),))),"")

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.

0

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:

https://onedrive.live.com/redir?resid=4FA287BBC10EC562%21398

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 ...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .