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!
Country | Travel log | Travel date | Extract country | Extract date |
United States | Hong Kong, United States | 2021-06-07, 2021-10-04 | United States | 2021-10-4 |
Brazil | Brazil, China, Australia | 2022-07-12, 2022-06-02, 2022-05-09 | Brazil | 2022-07-12 |
Canada | United Kingdom | 2021-11-11, 2021-12-12 | No record | No record |
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
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.
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.