How to do multiple lookup from values in 1 column to another?

[USER132991]

Hi Team,

I am new to Alteryx and currently working on a problem that I am not able to find a solution to. So the problem is like this:

Table 1:

A large dataset with millions of rows having the below columns of interest:

ID	Manufacturer
1	x-13_ManufacturerA_255
2	Z-24_ManufacturerB_55
3	42_ManufacturerC
4	10_ManufacturerD
5	ManufacturerA
6	ManufacturerB

Table 2: It is an input table wherein I would put the list of Manufacturers for which I need the data from Table 1:

Manufacturer

ManufacturerA

ManufacturerB

ManufacturerC

ManufacturerD

I need the final output like this.

ID	Manufacturer
1	ManufacturerA
2	ManufacturerB
3	ManufacturerC
4	ManufacturerD
5	ManufacturerA
6	ManufacturerB

So the heart of the problem is: Table 1 is a very large dataset having a column of Manufacturer name. Now, this column was filled manually and thus have random extra characters leading or trailing. I want to get the data filtered from Table 1 for the n number of Manufacturers which I can insert in the input file Table 2. Had there been only 1 manufacturer data I needed then I would have simply applied "Contains" formula to create a flag. But here there are multiple manufacturers needed. 

Please let me know the solution for this. I tried some but as the table is big it would become very heavy to run.

Thanks in advance!

[OllieClarke · Accepted solution]

Hi @USER132991 
This is a great usecase for the find and replace tool. If you set it up as so (in append mode)

Then drop the original manufacturer column, and rename manufacturer2

You should have what you need.

Hope that helps,

Ollie

[grazitti_sapna · Accepted solution]

Hi @USER132991 , i guess find and replace tool will be the best approach to use in such use case.

Please refer to the workflow and screenshot.

I hope this helps.

Thanks.

[USER132991]

Thank you, Ollie. Let me try that with my whole dataset. This should work! 🙂