Look up and extract information in another column

Question

Hi Community

Below is a sample of my data set. I'm trying to extract the information into new columns (in red font) . Basically I want to check if they travelled to the country and to return their date of travel. Any help is appreciated!

CountryTravel logTravel dateExtract countryExtract dateUnited StatesHong Kong, United States2021-06-07, 2021-10-04United States2021-10-4BrazilBrazil, China, Australia2022-07-12, 2022-06-02, 2022-05-09Brazil2022-07-12CanadaUnited Kingdom2021-11-11, 2021-12-12No recordNo record

flying008 · Answer

Hi, @hgrego

And there is a easy way with formula today.

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

flying008 · Answer

Hi, @hgrego

Maybe this is your want :

1- Formula of  [ Extract_date ] :

Trim(IIF([Country]!=Trim([Travel log]),Null(),GetWord([Travel date],  [Tile_SequenceNum]-1)),",")

******

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

AngelosPachis · Answer

Hi @hgrego ,

Here's one way you can do it by splitting the different travel logs per country into rows and checking whether the records contained there matches the values in the country field. If the second record matches the value, then the second date will be picked up by using the GetWord function.

Hope that helps,

Angelos

sample_workflow.yxmd