Excel : If value is in column A, then delete cell in column B Closed

Question

Hello,

This is what I want to do :

Column A :

value1
value2
value3
value4
value5

Column B :

value1
value22
value33
value44
value5

If value1 is inside column A (anywhere in the column), delete cell in Column B.

Why I want to do this ?

I have a list of urls that are dead in column A and a list of urls in column B, I want to delete the dead urls in column B.

Thanks.

venkat1926 · Answer

one way is 
data is like this 

value1 	value1 
value2 	value22 
value3 	value33 
value4 	value44 
value	value5 

in 1 type the formula
=IF(A1=$B$1:$B$5,"",B1)
copy it down

c will be final result. of B column

Johan222 · Answer

I tried that but it's not working

But I want is to check the whole column A and the whole column B and keep only the results that are in both columns.

Thanks.

TrowaD · Answer

Hi Johan,

Use this array formula:
=SUM(IF(B1=$A$1:$A$5,1,0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter.

Now you can drag the formula down.
Apply auto filter and filter on 1.
Now delete the URL's visible in column B.

Best regards,
Trowa

Johan222 · Answer

Hello,

Thanks but there is an error in the formula, I have excel 2013 and an error message appears when I enter this formula.

I tried with something very easy to create an array formula and it worked with =sum(1+1), it became {=sum(1+1)}

Do you have any other formula without errors ?

Thanks.

TrowaD · Answer

Hi Johan,

Could you post your workbook (with the formula's implemented) using a filesharing site like www.speedyshare.com or ge.tt?

It looks to me that like you have been given two formula's which yields the results you are after.

Best regards,
Trowa

Excel : If value is in column A, then delete cell in column B

5 responses

Similar discussions

Subscribe To Our Newsletter!