Find & Replace Across Multiple Columns Using Text Input Lookup

[malves2011]

Hello,

This is my first time posting to the Alteryx community and I was looking for some assistance. I'm attempting to do a find and replace across multiple columns and want to bring in a numeric value to replace a text value. I'd like to essentially have a lookup performed on 4 different text inputs that contain the lookup text and corresponding value I'd like to replace. I've listed an example below for how my columns look and the 4 different manual input texts I've created.

AA1	BB1	CC1	DD1	EE1	FF1	GG1	HH1	AA2	BB2	CC2
Apple	Sugar	Tomato	Milk	Sugar	Tomato	Apple	Sugar	Apple	Tomato	Apple
Banana	Salt	Carrot	Juice	Salt	Carrot	Banana	Salt	Banana	Carrot	Banana
Orange	Pepper	Bean	Water	Pepper	Bean	Orange	Pepper	Orange	Bean	Orange
Kiwi	Sugar	Corn	Soda	Salt	Corn	Kiwi	Pepper	Kiwi	Corn	Kiwi
Fruit	Value		Vegetable	Value		Spice	Value		Drink	Value
Apple	1		Tomato	10		Sugar	5		Milk	7
Banana	2		Carrot	8		Salt	4		Juice	5
Orange	3		Bean	6		Pepper	1		Water	3
Kiwi	4		Corn	4					Soda	0

Any assistance would be very much appreciated!

[grossal]

Hi @malves2011,

Welcome to the community!

here is a quick example how to do this:

Output:

What happens:

1) I add a Record ID

2) Transposing the data into rows

3) Union Tool with "Auto Config by Position"

4) Find and Replace to replace the value

5) Cross tab to bring them back into the original form

Workflow attached. Let me know what you think.

Best

Alex

[malves2011]

Works great! Thanks!

[grossal · Accepted solution]

That's great! @malves2011 

Would you mind accepting the post as a solution? This helps others to find it more quickly if they have similar problem.