Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Parsing Data that matches

Topic Labels: Formulas
Solved
Jump to Solution
437 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi,

I am trying to create a formula that will associate tags with a given row if that row contains text that matches entries in a row from another table.

For example, we have a table that contains a set of emails received:

Lisa spoke to a at b company
Kyle spoke to c at d company
Judy spoke to e at f company

Another table contains a list of names such as:

Judy
Kyle
Megan

I want to create a field on the messages table that will show Judy and Kyle as associated tags for that message because the strings Kyle and Judy match the entries in the list of names.

Appreciate any help!

Andy

1 Solution

Accepted Solutions

Hi @andy_duong,

What is the extent of the list of names in the other table? If it’s a short list, you can write a conditional statement to look out for a specified list of people and return their names in a list. Here’s a formula for that:

IF(
   FIND(
      "Judy",
      Notes
   ),
   "Judy" & ", " & "\n"
) 

& 


IF(
   FIND(
      "Kyle",
      Notes
   ),
   "Kyle" & ", " & "\n"
) 

& 

IF(
   FIND(
      "Lisa",
      Notes
   ),
   "Lisa"
)

If instead you have a larger table with a growing list of names, you may need to turn to the scripting block to write something to account for that.

See Solution in Thread

1 Reply 1

Hi @andy_duong,

What is the extent of the list of names in the other table? If it’s a short list, you can write a conditional statement to look out for a specified list of people and return their names in a list. Here’s a formula for that:

IF(
   FIND(
      "Judy",
      Notes
   ),
   "Judy" & ", " & "\n"
) 

& 


IF(
   FIND(
      "Kyle",
      Notes
   ),
   "Kyle" & ", " & "\n"
) 

& 

IF(
   FIND(
      "Lisa",
      Notes
   ),
   "Lisa"
)

If instead you have a larger table with a growing list of names, you may need to turn to the scripting block to write something to account for that.