Highlight entry based on list

S

Sorna2020

New Member
Joined
Jun 10, 2016
Messages
6
I have a list of 5,000 contacts created for an email blast that includes email address, first and last name, and company name.
I have a separate list of company names I do not want to receive an email.
I want to create a formula (or conditional formatting?) that would compare the 2 lists and highlight any entry on my contact list if their company name appears on my no-email list (it is possible for just a portion of the company name to be on either list- I.e. Bay Canter- Florida may be on my contact list but it may be listed as Bay Center on my no-email list.)

Example:

Contact ListNo Email List
Sam CarlsonSam@email.comAthena CorpGeorge Hill
John Doejohnd@email.comBay Center- FloridaGeorgetown University Hospital
Sally Smithsmith@email.comGeorge Hill, Inc.Guadalupe Regional Medical Center
Jeremy JohnsonGW-DWTN@email.comGrace Waters- Downtown GAHCA Bayshore
Sarah BentSarahBen@email.comHospital 1Houston Methodist Hospital
Nathan MitchellMitchellN@email.comJohn HopkinsJohn Hopkins
Hank CarlsonHC_MC@email.comMedCenterJones Memorial
Jennifer Waltersinfo@RRHC.orgReflect and Receive Healing ClinicMedCenter
William Joneswj_1@email.comSerenity PathMountains Community Hospital
Preview Breast Screening
Unity Medical Center (MM11)
VAMC Dayton Ohio
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
How is this...
Book4
ABCDE
1Contact ListNo Email List
2Sam CarlsonSam@email.comAthena CorpGeorge Hill
3John Doejohnd@email.comBay Center- FloridaGeorgetown University Hospital
4Sally Smithsmith@email.comGeorge Hill, Inc.Guadalupe Regional Medical Center
5Jeremy JohnsonGW-DWTN@email.comGrace Waters- Downtown GAHCA Bayshore
6Sarah BentSarahBen@email.comHospital 1Houston Methodist Hospital
7Nathan MitchellMitchellN@email.comJohn HopkinsJohn Hopkins
8Hank CarlsonHC_MC@email.comMedCenterJones Memorial
9Jennifer Waltersinfo@RRHC.orgReflect and Receive Healing ClinicHank Carlson
10William Joneswj_1@email.comSerenity PathMountains Community Hospital
11Preview Breast Screening
12Unity Medical Center (MM11)
13VAMC Dayton Ohio
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C10Expression=COUNTIF($E$2:$E$13,$A2)=1textNO
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top