How to compare 2 columns in excel

0
votes

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

2
excelgoogle-sheetsexcel-formulaformula
B3, is the vaule "sevda" or "sevdaa"? – NickSlash
Have you tried FIND() instead of MATCH()? – imtheman
@imtheman i count make "find" work. – goksel
@pnuts google. see it here docs.google.com/spreadsheets/d/… – goksel

2 Answers

1
votes

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.

1
votes

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