How to Match a keyword in a cell using a different data set

[vishwanathans]

Hi All,

I am pretty new to Alteryx. I am trying to explore an option, if i can just search a keyword in the cell and match the data with different data set.

Example below.

This is my description.

DESCRIPTION

03-Nov-2020 MARTINS/MARCO MR TORONTO PEARSON/VANCOUVER INTL/TORONTO PEARSON 106

08-Jan-2021 FLANAGAN/AISLING TERESA MS EDMONTON INTL/TORONTO PEARSON 16001

18-Feb-2021 ARAM/HEIDI MRS  205

06-Feb-2021 MBALE/JIBIKILA DR  1057 AND 1062

05-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR  1057 1067

12-Feb-2021 TAZI/ABDELAZIZ MR  1028

17-Feb-2021 RAIS/SALIM DR  12001

17-Feb-2021 RAIS/SALIM DR MONTREAL DORVAL/TORONTO PEARSON/MONTREAL DORVAL 12001

01-Apr-2021 RAIS/SALIM DR  12005

29-Mar-2021 RAIS/SALIM DR MONTREAL DORVAL/CALGARY INTL/MONTREAL DORVAL 12005

26-Mar-2021 RAIS/SALIM DR  12004

26-Mar-2021 RAIS/SALIM DR MONTREAL DORVAL/TORONTO PEARSON/MONTREAL DORVAL 12004

04-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR  406

11-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR  1059

15-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR EDMONTON INTL/VANCOUVER INTL/EDMONTON INTL 07254

16-Feb-2021 SIDDIQUI/SHAH MOHAMMED ZAIN MR  07254

05-Feb-2021 JAGO/AOIFE ANNE MS  1216

05-Feb-2021 LAMARI/SOFIANE MR  124104

17-Feb-2021 MARUCA/BRIGIDA MS MONTREAL DORVAL/CALGARY INTL/EDMONTON INTL/CALGARY INTL/MONTREAL DORVAL 07003

21-Feb-2021 MARUCA/BRIGIDA MS  07003

07-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR CALGARY INTL/EDMONTON INTL/CALGARY INTL 1057 1067

10-Feb-2021 PALMA LOPEZ/CESAR AUGUSTO MR  1057 1067

19-FEB-2021 PEPIN/NATHALIE MARIE MS Professional Fee NA

10-Feb-2021 ARAM/HEIDI MRS  901

18-Feb-2021 CARMICHAEL/PETER MR  1077 AND 1024

19-Feb-2021 MOURANI/VIVIANE MS  NA

16-Feb-2021 MARTINS/MARCO MR TORONTO PEARSON/VANCOUVER INTL/TORONTO PEARSON 106

Name should be matching data and i need the target value against that.

NAME	TARGET
MARTINS MARCO	1
FLANAGAN AISLING TERESA	2
ARAM HEIDI	3
MBALE JIBIKILA	4
TAZI ABDELAZIZ	5
RAIS SALIM	6
SIDDIQUI SHAH MOHAMMED ZAIN	7
JAGO AOIFE ANNE	8
LAMARI SOFIANE	9

Also please be informed that, there isn't any definite pattern in the in the data set.

Thanks in advance.

Srinivasan Vishwanathan

[Emil_Kos]

Hi @vishwanathans,

I have prepared a workflow for you:

Output:

Find and replace is a tool to go but first, we need to use text to column to prepare the data and split it by rows. 

[Emil_Kos · Accepted solution]

Hi @vishwanathans,

If you need to check if all the words are present this is a little bit more robust solution:

Output:

In the previous solution, I have only checked if one of the worst from a second data set is present. In this one, I am checking if all the words are present. 

[vishwanathans]

Thanks Emil. You are true genius.

Can you also help me with this if you can.

Is there any way that i need to just carve out the details between 2nd _ and 3rd _ from the data below.

"212361058_840111_WARD/CHANDRA DANIELLE_2/4/2021_San Antonio"

[Emil_Kos]

Hi @vishwanathans,

I am not a genius... but I am very grateful for a kind word 😄

Propably the simplest solution is using text to column + select tool.