Euro5
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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),"","no t found")
=IF(COUNTIF('Net Rates 2'!A:A,Summary!A17),"","no t found")
=IF(COUNTIF('Net Rates 1'!A:A,Summary!A17),"","no
=IF(COUNTIF('Net Rates 2'!A:A,Summary!A17),"","no
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
In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
=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"