Lookup with Values segregated by commas

[Kaish]

Hi All, I have a requirement in which I need to do lookup with the input data from other file even if the data has one vale and the lookup data has multiple values with comma as a delimiter. 

ex.  Input 1                                                                                                 Input 2                      

   Criteria         ID             Comment                                                           Criteria          ID             Comment 

     A            123,132                                                                                      A               132              Valid

     B               145                                                                                          B               145              Valid

     C             123,789                                                                                     C               123              Remark

     D              555                                                                                           D               111              Valid

     E           666,777,111                                                                                E                777              Valid

     F              454,676                                                                                    F                 222              Remark

    G               767                                                                                           G                767              Valid

Output :

Criteria         ID                       Comment          

     A            123,132                  Valid                                                                    

     B               145                      Valid                                                                

     C             123,789                 Remark                                                                    

     D              555                                                                                                                

     E           666,777,111             Valid                                                                          

     F              454,676                                                                                         

    G               767                        Valid

Here as you can see in the output even if one data is matching the output will have the comment of 2nd Input.

Can anyone help me out ?

[DataNath]

Hey @Kaish, in the first row of your data, you have 123 (Remark, according to your 2nd input) and 132 (Valid, according to your 2nd input). In your expected output you have 'Valid' here - is there any particular logic for cases like this? I started building out a workflow using Find Replace, but this only appends the first found value so would put 'Remark' on this row.

[OTrieger]

@Kaish 
First use Data to Column tool, then add 2 Find and Replace Tool for each of the new columns. Add a Formula that check if any of the columns has a comment if yes keep one if not keep Null and then remove all the unneeded columns.

[Kaish]

Hi @DataNath, there is no particular criteria, you can add anything in the comment data while creating, I just want my look ups to match the data.

[Kaish]

Hi @OTrieger, the data that I've shared is just a small sample of my big dataset, my data has more than 10k values, and the comma separated values are not limited to 3 or 4 numbers, it can be more.

[binuacs]

@Kaish use the text-column and jon tool

[OTrieger]

@Kaish 

In this case you could do it in a batch macro, Control Parameter will be the entries or the RecordID (you will need to change it to sting if you which to use it for the Control Parameter), so each entry will be handled in the same manner and then at the end you will have all nicely merged. Add record ID to have a connecting point

In the batch macro add a Filter tool that equal to ID in in your case.
The put a Text to Colum Tool and select Slip to rows. That will take care of as many values that will be
Not join with the 2nd set, now you will get the Comment.
Connect the J to Batch Macro output
What ever coming out from the Batch Macro Join based on RecordID and then you will have the comment for each and every one of the lines, do not forget to add a union for these rows that do not have description

[DataNath · Accepted solution]

@Kaish if I'm understanding you right and there's no criteria, you just need to take one of the comments, then the Find Replace approach would work where you just look for a value against a lookup table:

[Kaish]

Thanks @DataNath , I totally forgot we could use find and replace tool for this. Thank you for the solution 

[Kaish]

Hi @DataNath  what if we have 2 column to lookup, like the criteria column and ID column both we have to match, what we can do in this case ? My WF needs this logic for 2 values to lookup for.