In Google Sheets, How Do I Produce a Column of Non-Dupe Values Between Two Columns

246 views Asked by At

In this situation, Column A and Column B have a lot of similar values, but are non-identical and not in the same order. Without manipulating the two source columns, how can I create a Column C which only includes non-duplicate values? Given Col A Values, Remove Col B Values, Produces Col C Values

If ColA is Ed, Ned, Kyle, Myles, Ruby, Crystal, Eric, Jimi
And ColB is Kyle, Ruby, Crystal, Eric, Ed
Then ColC Would Return is Ned, Myles, Jimi

I've tried FILTER() and QUERY() combinations, but to no avail. I've tried ARRAYFORMULA({}) to create a stack, but that hasn't worked either.

Ideally, this would be done without needing to use the ISERROR(SEARCH()) and other multi-step formulae.

Thanks

=FILTER( ARRAYFORMULA({FILTER($D$11:$D,$D$11:$D<>"");FILTER(Import!$B$3:$B,Import!$B$3:$B<>"")}), ARRAYFORMULA({FILTER($D$11:$D,$D$11:$D<>"");FILTER(Import!$B$3:$B,Import!$B$3:$B<>"")})<>Import!$B$3:$B)

I expected a stack of my two columns in question using the ARRAYFORMULA({}), and then to be able to filter out the values which appeared in my second column, but array filtering doesn't quite work.

3

There are 3 answers

1
Harun24hr On BEST ANSWER

You may use QUERY() function.

=QUERY(A1:A,"select A where not A matches '" & TEXTJOIN("|",1,TOCOL(B1:B,1)) & "'")

enter image description here

1
Stack On

Try:

=FILTER(A1:A, ISNA(MATCH(A1:A, B1:B, 0)))

Output:

OUTPUT

0
The God of Biscuits On
=unique(tocol(A:B,1),,1)

Stack all the names in a single column (minus blank cells using the ignore argument of tocol) then use the exactly_once argument of unique to get those names which are present only once in the combined list.