Comparing cell value list separated by / but exclude a text within search using Formula

[MHamid]

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).

=TEXTJOIN(" / ",TRUE,IF(COUNTIF($F13,"*"&Admin!$A$2:$A$6&"*"),Admin!$A$2:$A$6,""))

My current workaround was to add a new column (BY) with the following 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:

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:

Excel file for Submission 

CISO
CTI
GCB Technology
GFT
ICG Technology

Thank you,

 

[Eric W]

I'm very hazy as to what you want, since I can't tell the actual cells where your data is, and what your exact requirements are. But maybe this might work for you:

=TEXTJOIN(" / ",TRUE,IF(ISNUMBER(SEARCH(" "&Admin!$A$2:$A$6&" "," "&$F13&" ")),Admin!$A$2:$A$6,""))

 

[Peter_SSs]

I believe that Eric's solution is what you want, but for the future please you investigate XL2BB for providing sample data. If that is not possible then just copy/paste your small ranges from Excel into the forum, but do not use the vba tags for sample data as it it makes it hard for helpers to get that data into Excel to test with.

 

[MHamid]

Hello,

Peter - sure no problem. I can't use XL2BB due to work restrictions.

Eric - Your formula did the trick. Apologies for not providing the actual locations. I will explain better with column locations in the future.


Thank you both

 

[Eric W]

Glad we could help, and thanks for the followup.

 

[Peter_SSs]

Peter - sure no problem. I can't use XL2BB due to work restrictions.

Fair enough. If you ask further questions it would be a good idea to state that in the first post or people like me will likely keep asking you for XL2BB samples.

And just to confirm, if you just copy/paste from Excel like below and tell us what range is shown at least we can copy reasonably easily.

All Businesses	Vital Business
ICG Operations / Operational Effectiveness
CAO / CISO / COO / CSS / CTI / GCB Operations / Operational Effectiveness	CISO / CTI
GCB Operations / Operational Effectiveness