Dictionary Data: enhance meanings in column D through column A

Hello every one,

i have a wordlist (dictionary Data) which contains of four Columns ABCD (about 70 000 entries in Column A):

A Words, B Transcription, C Grammar, D Meanings of words in A

Now some entries in Column D contain of reference to other words in Column A. For example:
________________________
before:
Col.A (words)...........................Col.D (Meanings)
W1 .................................... M1, M2, M3
W2 .................................... M4, W1 °°, M0
W3 .................................... W1 °°
W4 .................................... W1 °° (M5)
W5 .................................... W6 °°, M6, M7
W6 .................................... M8, M9

(The example is attached as excel file!)
_________________________

Now there are sometimes references to column A in column D instead of meanings. For example if one wants to know the meanig of Word "W3" then he/she should refer to "W1" cus both have the same meanings. The specific property of such refrences is that they are followed by "°°". Now i need a macro which looks the refrenced word in column A and put its corresponding meanings from column D and put them infront as new entry after "°°". According to our example the result would be:

________________________
after running the macro:
Col.A (words)...........................Col.D (Meanings)
W1 .................................... M1, M2, M3
W2 .................................... M4, W1 °°{{M1, M2, M3}}, M0
W3 .................................... W1 °°{{M1, M2, M3}}
W4 .................................... W1 °° {{M1, M2, M3}}(M5)
W5 .................................... W6 °°{{M8, M9}}, M6, M7
W6 .................................... M8, M9

_________________________

I have about 70 000 Words in Column A and a macro would save me alot of time. I thank each help and support in advance.

You have two different versions... W7°° and W6 °°

Will it be random like that? Some have spaces after the word reference and some do not? Can you clean that up to be consistent?

Also, with 70,000 entries, then we're also talking about W20000 °° too, right?

Wali, please revise your title -- it's impossibly specific.

Hello dear Shg,
thank you very much for the suggestion to change the titel. What about the new one? I hope that i will learn to be more precise sometime! :-)

Wali, I don't think a lack of precision was the problem! Try this, it seems to do what you want:

Sub x()

Dim rng As Range, rWord As Range, sRef As String, sFind As String

sRef = "°°"

Range("D2", Range("D2").End(xlDown)).Replace What:=" " & sRef, Replacement:=sRef, Lookat:=xlPart

For Each rng In Range("D2", Range("D2").End(xlDown))
    If InStr(rng, sRef) > 0 Then
        sFind = Mid(rng, InStr(rng, sRef) - 2, 2)
        Set rWord = Range("A2", Range("A2").End(xlDown)).Find(What:=sFind, Lookat:=xlPart)
        If Not rWord Is Nothing Then
            rng.Replace What:=sRef, Replacement:=sRef & "{{" & rWord.Offset(, 3) & "}}", Lookat:=xlPart
        End If
    End If
Next rng

End Sub

EDIT: this assumes your word refs (eg W1) are two characters so if your actual data are different the code will have to be revised.
EDIT2: this should overcome the above:

Sub x()

Dim rng As Range, rWord As Range, sRef As String, sFind As String, v, i As Long

sRef = "°°"

Range("D2", Range("D2").End(xlDown)).Replace What:=" " & sRef, Replacement:=sRef, Lookat:=xlPart

For Each rng In Range("D2", Range("D2").End(xlDown))
    If InStr(rng, sRef) > 0 Then
        v = Split(rng, ",")
        For i = LBound(v) To UBound(v)
            If InStr(v(i), sRef) > 0 Then
                sFind = Trim(WorksheetFunction.Substitute(v(i), sRef, ""))
                Set rWord = Range("A2", Range("A2").End(xlDown)).Find(What:=sFind, Lookat:=xlWhole)
                If Not rWord Is Nothing Then
                    rng.Replace What:=sRef, Replacement:=sRef & "{{" & rWord.Offset(, 3) & "}}", Lookat:=xlPart
                End If
                Exit For
            End If
        Next i
    End If
Next rng

End Sub