Lookup with Values segregated by commas

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 ?

Workflow

Datasets

Input

Output

Help

Accepted answers

DataNath

@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:

FindReplaceComment.yxmd

All comments

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.

binu_acs

@Kaish use the text-column and jon tool

Join_03092024.yxmd

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

FindReplaceComment.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335