x
Solved

# Excel -remove all column B cell values from Column A

Posted on 2016-08-30
Medium Priority
84 Views
Greetings,

I have an XLS with a tab: 2 columns of email addresses. I would like to remove all cell values in Column B, from Column A.

Is there an efficient way to do this?

Thank you!
JohnD
bulkChange.xlsx
0
Question by:John Darby

LVL 43

Assisted Solution

Saqib Husain, Syed earned 1000 total points
ID: 41777383
Enter this formula in C2 and copy it all the way to the end of the data
Apply Autofilter to the data
Filter on column C by unchecking #NA
Clear cell contents from column A
Unfilter
0

LVL 1

Author Comment

ID: 41777387
Greetings Saqib, which formula are you suggesting? :)
0

LVL 20

Accepted Solution

Serena Hsi earned 1000 total points
ID: 41777388
You could.. in Column C use this formula:

=VLOOKUP(\$B2,currentlist,1,0)

For column A, I selected everything below the header and named the range as currentlist

Once you have your matches after having copied the formula in Column C; you can select Column C and copy/paste values.

Then Sort by ascending/descending, and delete the rows that show an email address; you can do a find/replace on the #N/A text in that same column.
0

LVL 43

Expert Comment

ID: 41777403
Sorry, the formula is

=match(a2,b:b,0)
0

LVL 36

Expert Comment

ID: 41777541
Please find the attached in which a conditional formatting in col.B will highlight the emails which are found in col. A and col. C contains a formula that tells you where exactly the email from col. B is found in col. A.
See if this helps.

If you want to delete the emails from col. B, you may apply filter on color and delete the cell contents.
bulkChange.xlsx
0

LVL 1

Author Comment

ID: 41778416
Thanks guys...let me go look this AM and get back to you! Thanks again!
0

LVL 1

Author Closing Comment

ID: 41778929
Thank you!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.