Separate Cell Values with Comma and transfer into Column

[ivandgreat]

Hi,

I wanted to separate my cell values with comma into a column

Item	DepIDs	No	IDA
1	2000	1	2000
2	2000, 2001	2	2001
3	2001, 2000, 2002	3	2002

<tbody>
</tbody>

the output will be below table,

Item	IDA	DepIDs
1	2000	2000
2	2001	2000
3	2001	2001
4	2002	2000
5	2002	2001
6	2002	2002

<tbody>
</tbody>

br,
ivan

 

[patel45]

Suppose table starting in A1

Sub a()
LR = Cells(Rows.Count, "A").End(xlUp).Row
dcol = 6
drow = 2
n = 1
Range("F1") = Range("A1")
Range("G1") = Range("D1")
Range("H1") = Range("B1")
For j = 2 To LR
  If InStr(Cells(j, 2), ",") = 0 Then
    Range("F" & drow) = n
    Range("G" & drow) = Range("D" & j)
    Range("H" & drow) = Range("B" & j)
    drow = drow + 1
    n = n + 1
  Else
    ar = Split(Cells(j, 2), ",")
    For i = 0 To UBound(ar)
      Range("F" & drow) = n
      Range("G" & drow) = Range("D" & j)
      Range("H" & drow) = ar(i)
      drow = drow + 1
      n = n + 1
    Next
  End If
Next
End Sub

 

[ivandgreat]

Thanks patel45, I have tried it and works fine. Any idea how to make it more faster, I'm having more than 1000 rows with 10 to 50 values to work on.

Thanks a lot.