Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

Find and comment based on find

Either in vba or in excel formula on Summary sheet.

I need to search for each of these phrases below on the 'Net Rate 1' & 'Net Rate 2' sheets.
On the 'Summary' sheet, I need to provide comment for each sheet/service:
 "Domestic Priority Overnight" found on Net Rate 1
 "Domestic Priority Overnight" found on Net Rate 2
OR
 "Domestic Priority Overnight" not found on Net Rate 2
 "Domestic Priority Overnight" not found on Net Rate 2

 "Domestic Priority Overnight"
 "Domestic Standard Overnight"
"Domestic 2 Day AM"
 "Domestic 2 Day"
"Domestic Express Saver"
 "Puerto Rico Southbound Priority Overnight (Based on Custom rates [Envelope] [Pak] [Package] ) "
"Puerto Rico Northbound Priority Overnight (Based on Custom rates [Envelope] [Pak] [Package] ) "
"Domestic 1Day Freight"
"Domestic 2Day Freight"
"Domestic 3Day Freight"
"International Export Priority Freight - Door to Door"
"International Export Economy Freight - Door to Door"
 "International Import Priority Freight - Door to Door"
"International Import Economy Freight - Door to Door"
"Puerto Rico Northbound 1Day Freight (Based on Custom rates [Package] )
 "Puerto Rico Northbound 2Day Freight (Based on Custom rates [Package] ) "
 "Ground - Ground Single-Piece Rates"
 "Ground Deferred"
 "US to Canada"
 "Domestic First Overnight Non-Freight"
  "International Export Priority"
 "International Import Priority"
 "International Export Economy"
 "International Import Economy"
"Puerto Rico Southbound Economy (Based on Custom rates [Package] ) "
"Puerto Rico Northbound Economy (Based on Custom rates [Package] ) "
 "Ground - Home Delivery Single Piece Rates"
 "Ground - Ground Multiweight Rates"
"Package Type(s):PRP"
find.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I pasted your list of shipping types in Summary worksheet cell A17, then used the Home...Replace menu item to delete the double quotes from the pasted text. I could then copy down the following formulas to test your worksheets:
=A17 & IF(COUNTIF('Net Rates 1'!A:A,Summary!A17)," found"," not found") & " on Net Rates 1"
=A17 & IF(COUNTIF('Net Rates 2'!A:A,Summary!A17)," found"," not found") & " on Net Rates 2"
If the purpose of the formula is to highlight missing shipping types, I suggest simplifying the message to return an empty string if the type was found, or the words "not found" if it was missing. Furthermore, you don't need to specify the name of the worksheet if you put the results of the two formulas in separate columns.
=IF(COUNTIF('Net Rates 1'!A:A,Summary!A17),"","not found")
=IF(COUNTIF('Net Rates 2'!A:A,Summary!A17),"","not found")
Avatar of Euro5

ASKER

Thanks again!
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015