Question for those Excel experts!

[dotcomer]

So i have an excel spread sheet. I have a list of cities in column A. In Column H we have a list of cities we already mailed advertisements too. I need to remove the cities we already sent advertisements to from column H, out of column A.

So basically the cities in Column H are our bad cities. Column A is our good.

How do I remove the cities that are in Column H from Column A?

 

[FDibbins]

Short answer - with VBA. Formulas do not affect other cells, only the 1's they reside it.

An alternative might be to use filters, then filter out the 1's you have already contacted?

 

[Amanda Wilso]

Manually, you can use data filtering to determine the unique values. Make sure the column has a label at the top of it, then select a cell in the column. Display the Data tab of the ribbon and click Advanced in the Sort & Filter group. Use the controls in the resulting dialog box to specify that you want to copy the unique values to another location which you specify.
You can also use a formula to manually determine the duplicates in the list. Sort the values in the column, and then enter the following formula in cell B2:
=IF(A1=H1,"Duplicate","")

 

[dotcomer]

Is there not any formula to just remove the cities in column A that are in Column H?

 

[dotcomer]

ADVERTISEMENT

How to filter it? All i know how to do is remove duplicates

 

[dotcomer]

I put that formula in B2 and it did not do a thing

 

[Joe4]

ADVERTISEMENT

Is there not any formula to just remove the cities in column A that are in Column H?

What Ford said in his first line is correct:

Short answer - with <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym>. Formulas do not affect other cells, only the 1's they reside it.

Excel formulas cannot remove anything. All they can do is calculate and return something to the cell that the formula itself resides in.

You got some good suggestions on how to handle it:
- With VBA
- With Filters
- A roundabout, manual solution using formulas (i.e. identifying duplicates, then maybe manually deleting them)

I can add another potential solution - use Microsoft Access instead.
This is a simple "Unmatched Query" in Access. There is even a Wizard in Access to walk your through setting it up.

 

[FDibbins]

Is there not any formula to just remove the cities in column A that are in Column H?

Perhaps you missed the 1st line of my post on post #2?

 

[FDibbins]

How to filter it? All i know how to do is remove duplicates

Make sure your entire data range is highlighted (in case there are any empty cells to mess things around)
Click the HOME tab/Editing/Sort and Filter/Filter
you will see all your heading row cells now have a small drop-down in them, click the 1 you want to filter on and uncheck whatever needs to be unselected to show what you need

 

[dotcomer]

I seen what the first line was but never even heard of VBA. What about highlighting whats in Column H and A. If the value exist in Column H can I highlight those in column A? Let me rephrase this, I know I can but how