SOLVED

Match a tag name

Contributor

Hi Team

 

I am trying to match a tag name in Column "A" to a tag name in Column "B" that has data with comma delimiter formatting within and return a "Yes" in Column "C" if found. 

 

Any help would be greatly apricated.

 

See attached sample.

 

Best regards

 

Frank

 

6 Replies

@Frank145 

=IF(AND(OR(ISNUMBER(SEARCH($A$2:$A$209,B2))),ISNUMBER(SEARCH(",",B2))),"Yes","")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

if found yes.JPG 

@Quadruple_Pawn - it worked up until Row 72. Because my “A” column goes to row 209 and “B” only has data up until row 72 the remaining of “A” was not compared. 

@Frank145 

For example the entry of cell A204 "WhatsUpGold" is found in cell B68 and the formula returns "yes" in cell C68. Isn't this the expected result?

A204.JPGB68.JPG

Sorry for the confusion. let me try to explain what I am trying to do.
Within the newly attached sheet I have provided my real-world example.
The Data Explained:
- Within the Runbook Meeting Schedule I have a list of Applications (Tags) listed in column "A".
- Within the Planning list has in the "D" (Tags) column is the list of applications running on devices in the corresponding "A" cell (Device). Within the "N" column is a "Yes" or blank, which tells me if "Yes" then the Operations systems listed in "F" is EOL.

The Requirement:
Within Runbook Meeting Schedule column "B" I would like a formula to match the Tags listed in Runbook Meeting schedule column "A" to find the same App name in the PlanningList column "D" (Tags) and if there is a "Yes recorded in "N" (EOL) then add a "Yes" back into the corresponding cell "B" back in the Runbook Meeting schedule.

best response confirmed by Hans Vogelaar (MVP)
Solution

@Frank145 

=IF(SUM((--ISNUMBER(SEARCH(","&[@Tags]&",",","&Table13[Tags]&",")))*("yes"=Table13[EOL]))>0,"yes","")

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula returns "yes" if there is at least one device with the tag from column A of the Runbook Meeting Schedule and "yes" in column EOL of the Planning list.

tags.JPG

 

For example there are two devices which run on EOL and have the tag ACT. 

tags ACT.JPG

 

Pawn you are a genius!

This works great and is now deployed in our production workbook.

Thank you so much.

Best regards