Conditional joining using key

[Adityasa]

Hi,

I have one data set that looks like below:

Employee ID	Function
1	Apple
2	Ball
3	Cat
4	Ball
5	Cat
6	Apple
7	Apple

There's another data set that looks like this:

Employee ID	Specialty
1	Sweet
6	Sour
9	Bitter
21	Tasteless
24	Juicy
26	Salty

I want to add the Column of Specialty to first data set based on matching Employee IDs in the two tables, however only for those Employee IDs that Function as 'Apple'. Some Employee IDs with Function as 'Apple' may not be present in second table (like there's no Employee ID 7 in second table) - in these cases, I want the Specialty to be blank. For Employee IDs with other Functions, I want the Specialty to be blank. So the final output should look like this:

Employee ID	Function	Specialty
1	Apple	Sweet
2	Ball
3	Cat
4	Ball
5	Cat
6	Apple	Sour
7	Apple

Thanks.

[RishiK]

@Adityasa I think what you are looking to do is similar to a Right Outer Join like in the attached workflow.

This workflow checks the matches and then applies the Speciality column / value, and brings in the other data.

Let me know if this is what you were trying to produce. 

[Adityasa]

@RishiK 

What if the second table is like this? Here, I don't want the workflow to pick up 3 and 4 as sweet and sour respectively. I want them to be blank.

Employee ID	Specialty
1	Sweet
3	Sweet
4	Sour
6	Sour
9	Bitter
21	Tasteless
24	Juicy
26	Salty

[Maskell_Rascal]

Hi @Adityasa 

If the join you are making is more of a one off/specific use, you can use the attached workflow. If not, I'd need to know more about how you're attempting to filter/join the data.

This workflow uses an Advanced Join macro that enables you to join datasets based on a formula/condition.

Once I get my desired joins, I do a Find/Replace to attach them to the original data by Employee ID. 

If this solves your problem please mark answer as correct, otherwise let me know!

Cheers!

Phil