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

Closed
Posts
3
Registration date
Friday June 21, 2013
Status
Member
Last seen
June 24, 2013
-
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
-
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.

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
Posts
3
Registration date
Friday June 21, 2013
Status
Member
Last seen
June 24, 2013

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.
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
491
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
Posts
3
Registration date
Friday June 21, 2013
Status
Member
Last seen
June 24, 2013

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.
Posts
2848
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 25, 2022
491
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