converting a multiword row to synonym new rows (macro) --very very important

[osmanoca]

hello dear friends i need help about this. please help..
if anyone helps me will be grateful, indebted, thankful, obliged, appreciative
------------------------

i have rows that include multi words with comma in column A and they are in same meaning. i want to convert them to synonym dictionary. so all word will go in a new row and other words in same row will go next column row.

example:

Column A
1- to be frightened, to be afraid, to fear, to scare, to be scared, to worry, to be anxious

this row word must be new rows (in two columns) as much as number of words like below:

Column A rows
1-to be frightened
2-to be afraid
3- to scare
4-to be scared
5-to worry
6-to be anxious

Column B rows
1-to be afraid, to fear, to scare, to be scared, to worry, to be anxious
2-to be frightened, to fear, to scare, to be scared, to worry, to be anxious
3-to be frightened, to be afraid, to fear, to be scared, to worry, to be anxious
4-to be frightened, to be afraid, to fear, to scare, to worry, to be anxious
5-to be frightened, to be afraid, to fear, to scare, to be scared, to worry, to be anxious
6-to be frightened, to be afraid, to fear, to scare, to be scared, to worry


if anyone can help me for this really really i will be happy and many thanks for all want to help.
thanks thanks thanks from now...

 

[Peter_SSs]

Try this on a copy of your workbook.
It assumes data in column A starting at row 2 and results to go into columns B:C

Sub Synonyms()
  Dim a As Variant, b As Variant, bits As Variant
  Dim i As Long, j As Long, k As Long
  Dim s As String, tmp As String
  
  ReDim b(1 To Rows.Count, 1 To 2)
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = Replace(a(i, 1), " ", "|")
    If Len(a(i, 1)) > 0 Then
      bits = Split(Replace(s, ",|", ","), ",")
      For j = 0 To UBound(bits)
        tmp = bits(j)
        bits(j) = Empty
        k = k + 1
        b(k, 1) = Replace(tmp, "|", " ")
        b(k, 2) = Replace(Replace(Application.Trim(Join(bits)), " ", ", "), "|", " ")
        bits(j) = tmp
      Next j
    End If
  Next i
  Range("B2:C2").Resize(k).Value = b
End Sub

 

[osmanoca]

Thanks very very much dear Peter, this worked. you helped me. i hope the god helps you in your life....

Try this on a copy of your workbook.
It assumes data in column A starting at row 2 and results to go into columns B:C

Sub Synonyms()
  Dim a As Variant, b As Variant, bits As Variant
  Dim i As Long, j As Long, k As Long
  Dim s As String, tmp As String
  
  ReDim b(1 To Rows.Count, 1 To 2)
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    s = Replace(a(i, 1), " ", "|")
    If Len(a(i, 1)) > 0 Then
      bits = Split(Replace(s, ",|", ","), ",")
      For j = 0 To UBound(bits)
        tmp = bits(j)
        bits(j) = Empty
        k = k + 1
        b(k, 1) = Replace(tmp, "|", " ")
        b(k, 2) = Replace(Replace(Application.Trim(Join(bits)), " ", ", "), "|", " ")
        bits(j) = tmp
      Next j
    End If
  Next i
  Range("B2:C2").Resize(k).Value = b
End Sub

 

[Peter_SSs]

Thanks very very much dear Peter, this worked. you helped me.

Good news. Thanks for letting us know.