Return the text of a Duplicate from column to another column

[adrian.paul]

Hello,

I have a column with English text in Column A. The column B has the translation in another language of the cells from Column A. I applied Conditional formatting to Column A to find duplicates (if the newly introduced text was translated before). Now if it is finding a duplicate to the newly introduced text in Column A, next to the newly introduced English text I would like to return the translation of the upper found duplicate's translation, which is found in the Column B.

I attached a worksheet if my description it wasn't accurate enough. So, if A5 is the duplicate of A2 then I want somehow in B5 to have the text from the found duplicate's translation which is B2.

Thanks in advance for your help!
Adrian

[rabbita]

Hello,

I wrote a formula for you. Please find it in the attached file. I hope it is what you are looking for

Example_1.xlsx

BTW, don't you want to remove those duplicates from your file and then add the translation?

Regards,
Alex

[adrian.paul]

Hi Alex,

Your formula is amazing as you :-). Thanks for your help and I don't want to remove the duplicates, because even if they are the same but it will get different IDs, because they will be place in different places.

Thank you very much.
Adrian

[rabbita]

I'm really glad I could help! :D