Hello,
Need some advise on a formula I have created. I have a column with cells that have a list separated by " / " (All Businesses) that i have to compare to a list in a column on another sheet.
the Vital Business column in Sheet 1 is the desired result.
If I use the below formula, then I will see "Operational Effectiveness" as CTI and i don't want to see that (the reason why this is coming up as CTI is because one of the vital business is CTI and those letters are in the word Operational EffeCTIveness).
My current workaround was to add a new column (BY) with the following formula:
I tried to combine both formulas, but it doesn't even let me enter it as a valid formula.
anyone has any ideas how i can achieve what I need in one formula.
Sheet1:
List in another sheet:
Thank you,
Need some advise on a formula I have created. I have a column with cells that have a list separated by " / " (All Businesses) that i have to compare to a list in a column on another sheet.
the Vital Business column in Sheet 1 is the desired result.
If I use the below formula, then I will see "Operational Effectiveness" as CTI and i don't want to see that (the reason why this is coming up as CTI is because one of the vital business is CTI and those letters are in the word Operational EffeCTIveness).
Excel Formula:
=TEXTJOIN(" / ",TRUE,IF(COUNTIF($F13,"*"&Admin!$A$2:$A$6&"*"),Admin!$A$2:$A$6,""))
Excel Formula:
=SUBSTITUTE($F12," / Operational Effectiveness","")
I tried to combine both formulas, but it doesn't even let me enter it as a valid formula.
anyone has any ideas how i can achieve what I need in one formula.
Sheet1:
Rich (BB code):
Excel file for Submission
All Businesses Vital Business ICG Operations / Operational Effectiveness CAO / CISO / COO / CSS / CTI / GCB Operations / Operational Effectiveness CISO / CTI GCB Operations / Operational Effectiveness
List in another sheet:
Rich (BB code):
Excel file for Submission
CISO CTI GCB Technology GFT ICG Technology
Thank you,