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?

 

[Joe4]

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","")

That formula might not quite work out the way you would need it to, since the non-matched items are interspersed within your data, and not all at the end.
Use a COUNTIF function instead to count how many times each item in column A appears in column H. If they have not been mailed, it would return 0.

 

[Joe4]

I seen what the first line was but never even heard of <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>. What about highlighting whats in Column H and A. If the value exist in Column H can I highlight those in column A?

You can use a COUNTIF for that too.

Let's say that your data is in range A2:H1000.
Highlight cells A2:A1000 and use the following Conditional Formatting formula:

=COUNTIF(H:H,$A2)>0

 

[dotcomer]

Everything starts in row 2. Column A goes to 30650 and Column H goes to 1953

 

[Joe4]

Everything starts in row 2.

The formula I wrote in my previous post made that same assumption, so you should be able to use that formula "as-is".
Did you try it?

 

[dotcomer]

i copied and pasted it into I2 and it said FALSE. I highligted A2 through A30650

 

[dotcomer]

I highlighted A2 through A30650 then I pasted that formula in I2. The contents of H goes H2 through H1953

 

[dotcomer]

What if I just post the contents of column H in Column A then find the duplicates. How would I do this?

 

[Marcelo Branco]

Without VBA....maybe using Advanced Filter with a formula

Something like this (an example with a very small data set)

Data

	A​	B​	C​	D​	E​	F​	G​	H​
1​	List1​							List2​
2​	City1​							City3​
3​	City2​							City4​
4​	City3​							City8​
5​	City4​
6​	City5​
7​	City6​
8​	City7​
9​	City8​
10​	City9​
11​	City10​

<tbody>
</tbody>

Leave Z1 empty and insert this formula in Z2
=ISNUMBER(MATCH(A2,$H$2:$H$4,0))

go to Data > Advanced Filter and set
List range: $A$1:$A$11
Criteria range: $Z$1:$Z$2
Ok

You get something like this

	A​
1​	List1​
4​	City3​
5​	City4​
9​	City8 ​

<tbody>
</tbody>

Select the visible cells with data (excluding the cell with the header) and press F5 > button Special...
pick Visible cells only

in Data | Sort & Filter Group click in Clear

Right-click in one shaded cell and pick Delete > Shift Cells Up

Done! (uff...)

	A​	B​	C​	D​	E​	F​	G​	H​
1​	List1​							List2​
2​	City1​							City3​
3​	City2​							City4​
4​	City5​							City8​
5​	City6​
6​	City7​
7​	City9​
8​	City10​
9​
10​
11​

<tbody>
</tbody>

Hope this helps

M.

 

[Joe4]

i copied and pasted it into I2 and it said FALSE. I highligted A2 through A30650

OK, I think you just need to slow down, and carefully read and consider the responses you are being given.

Note what I said in the post with my formula:
You can use a COUNTIF for that too.

Let's say that your data is in range A2:H1000.
Highlight cells A2:A1000 and use the following Conditional Formatting formula:

[COLOR=#333333]=COUNTIF(H:H,$A2)>0
[/COLOR]

You had asked about highlighting, so I gave you a Conditional Formatting solution (which does that automatic highlighting).
So the formula goes in your Conditional Formatting settings, not in a cell on the Excel sheet.

If you do not know how to do this, see this link found with a simple Google search: https://support.office.com/en-us/ar...rmatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f

 

[FDibbins]

i copied and pasted it into I2 and it said FALSE. I highligted A2 through A30650

That formula needs to go into the Conditional Function feature, not into a cell.
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter that formula in the box