CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulaformula
goksel
goksel

Reputation: 4580

How to compare 2 columns in excel

I have Column A and Column B. What I want to do is copy Column C and remove rows which match/contain rows in Column B.

I have this formula but it doesn't remove containing rows, only matching:

=ARRAYFORMULA(IF(ISNA(MATCH(A:A,B:B,0)),A:A,""))

Any formula/query for this?

You can see it live here (please comment suggestion there): https://docs.google.com/spreadsheets/d/1EBJgRAt0UfyfP_zgv3RITNnN3VW3KXoeiIC9mDTjJL4/edit?usp=sharing

enter image description here

Upvotes: 0

Views: 394

Answers (2)

Euclid365BCE
Euclid365BCE

Reputation: 11

Excel - Copy column A to C - Highlight columns B & C - Home - Conditional Formatting - Highlight Cells Rules - Duplicate Values - Light Red Fill with Dark Red Text - Sort on pink cell colors for column C - Delete values shaded pink in column C - Done

Upvotes: 1

Geoffrey Fernandez
Geoffrey Fernandez

Reputation: 138

If you set the formula in column c row 1 as "=if(a1 = b1,1,0)", then you could just filter on values in column c = 1 and delete those rows. That way when you copy column c to another column or where ever else you are copying the data, you will not get the matching items to copy over.

Upvotes: 1

Related Questions