Help with Find & replace tool with two columns

[Charcho]

Hello,

My first time posting to the community and I was looking for some help.

I want to complete two columns, Value1 and Value2 with the values from key value table. I use the Find & Replace tool and the Union tool but I don't get to place the two values in a line.

Thank you all

The table to complete:

Item1	Item2	Value1	Value2
Fruits	Apple
Fruits	Banana
Vegetable	Tomato
Vegetable	Carrot
Spice	Salt
Spice	Sugar
Drink	Mik
Drink	Water

The key value table is:

Item	Value
Apple	B
Banana	A
Tomato	A
Carrot	B
Salt	B
Sugar	A
Mik	B
Water	A
Fruits	A
Vegetable	A
Spice	A
Drink	B

This is my result with duplicates. I would like to have the values in a line.

[binuacs · Accepted solution]

@Charcho Instead of union tool use the join tool and select the join by record position option and u select unwanted columns from the join tool

[Charcho]

Thank you @binuacs, it works correctly in the example I have put. However I work with a big dataset, and it doesn't work correctly because in some cells the value is mixed from the other item, i.e., in Value2 appears a value from Item1 and vice versa. Could it be due to record position option? there would be other way? Thank you

[Charcho]

Thank you Re: Help with Find & replace tool with two columns 

[SPetrie · Accepted solution]

Feed the result of one find/replace into the other.

[Charcho]

@binuacs , @SPetrieI , I have checked everything again and both solutions work perfectly. I was stuck in some wrong values and finally detected that I had selected 'Any Part of Field' in Find Section area... I changed it to 'Entire Field' and it works perfectly! Thank you