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

Closed
Johan222 Posts 3 Registration date Friday June 21, 2013 Status Member Last seen June 24, 2013 - Jun 21, 2013 at 02:57 PM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Jun 27, 2013 at 10:06 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 21, 2013 at 11:23 PM
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
0
Johan222 Posts 3 Registration date Friday June 21, 2013 Status Member Last seen June 24, 2013
Jun 22, 2013 at 04:41 AM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 24, 2013 at 12:00 PM
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
0
Johan222 Posts 3 Registration date Friday June 21, 2013 Status Member Last seen June 24, 2013
Jun 24, 2013 at 01:17 PM
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.
0

Didn't find the answer you are looking for?

Ask a question
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Jun 27, 2013 at 10:06 AM
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
0