Lookup Help ( Find & replace or join)

[tep_49]

Hi Everyone,

I have these two tables and I want to join them on name column 
providing the condition that if name from table 1 is available in table 2 then return the code
cant use join tool as there is no way to custom
would appreciate any help.

[rzdodson · Accepted solution]

@tep_49 I would personally do a Text to Columns for the second table and use the Split to Rows option in it. Then, use a Join tool to combine the two tables based on Name to bring in the Code field.

[ScottLewis · Accepted solution]

This is a job for the text to columns tool with the split to rows option selected. Once that's been done, the join is standard.

Notably, once text to columns has been applied, the second table actually becomes the output you want without joining, but I assume your actual data is more complex and thus you still want the join.

 

[tep_49]

Hi ,

Appreciate your help.

It works fine, I'm just afraid of the extra records I'm getting when doing the inner join using the name.

In my use case I want to ensure that I have only the records that are available in table 1.

any advice on that please?

[tep_49]

Hi 

thanks for the help.

I'm receiving duplicate rows when doing the inner join. 

and In my table 1 I dont have a unique idenifier to get the correct records from table1.

any advice on getting only the table 1 records after the join?

[rzdodson · Accepted solution]

@tep_49 you can put a Unique tool, connected to the J anchor, and then select all to drop duplicate entries.