Dictionary of synonyms - Enhance without creating duplicate

[wali]

Hi,

i have two worksheets "Original List" and "New List".
"Original List" contains three columns A, C and D. Column A contains words of First langauge sperated by ";", ",", or numbers like "1.", "2."... Column D contains the translation of words from A in Second language. For example:

It is now"Original List":
ColumA............................................................ColumnC............ColumnD
1. Beautiful; Attractive 2. Pretty 3. Lovely.............adj....................Schön; Attraktiv, hübsch, reizend, anziehend
goodlookin, lovely; nice.....................................adj....................hübsch; aziehend


I need to copy wordlist from "Original List" into "New List" worksheet in a broken and enhanced form.


It should be"New List":
ColumA............................................................ColumnC............ColumnD
Beautiful..........................................................adj. ..................Schön; Attrakiv, hübsch, reizend, anziehend
Attractive.........................................................adj. ..................Schön; Attrakiv, hübsch, reizend, anziehend
Pretty..............................................................adj. ..................Schön; Attrakiv, hübsch, reizend, anziehend
Lovely.............................................................adj. ..................Schön; Attrakiv, hübsch, reizend, anziehend, hübsch [ hübisch is from duplicate Lovely in second row]
goodlooking......................................................adj. ..................hübsch; aziehend
lovely..............................................................adj. ..................hübsch; aziehend [this should not be added cus it already exists 4th row (no duplicate rows should be allowed in Column A)]
nice.................................................................adj. ..................hübsch; aziehend

There should be now duplicate rows in Column A of "New List". And there should be no duplicates inside the cells of Column D in "New List".

I am uploading an example list and appreciate each help.

[daffodil11]

Here you go!

I tried posting the code but it picked up as a SQL injection.


The only part that's throwing me off is that you say no duplicates should be in D, but in both your post and your attached example you have included the duplicate hübsch.

[wali]

Dear daffodil11, thank you a million times. It really is great solution. I will try to find another code which I can run after yours to remove duplicates inside the Column D.
If someone else already has a solution it will be great.
What is yet to be done:
Cells in Column D have members seprated by ";" or ",". Now some of them contain duplicates, for example: "pretty, beautiful; pretty, nice". I need the duplicates to be removed like the word "pretty" is two times here.

[nilem]

Hi wali,
try this

Please Login or Register  to view this content.

[wali]

dear nilem, spasibo balshoi dlia washe pamoch (= thank you very much for your help)

I need the commas "," and semicolons ";" in the results. i will be very thankful if you could please modify the code so that it keeps the commas and semicolons in column D but removes duplicates.