Seperating out lists

[BenjaminDubai]

I have this

Cell 1 -/- Cell 2

1 -/- R1, R2, R3
2 -/- R1, R2, R3, R4, R5
3 -/-
4 -/- R1
5 -/- R1, R2, R3

and I would like to get to this

Cell 1 -/- Cell 2

1 -/- R1
-/- R2
-/- R3
2 -/- R1
-/- R2
-/- R3
-/- R4
-/- R5
3 -/-
4 -/- R1
5 -/- R1
-/- R2
-/- R3

WITHOUT doing it manually!

Any ideas?

 

[ravishankar]

Hi
Assuming your data is in sheet1, paste the following codes in the macro window ( Alt F8)

Sub elongate()
c = 1
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
y = Cells(a, Columns.Count).End(xlToLeft).Column
For b = 3 To y
Worksheets("sheet2").Cells(c, 1) = Cells(a, 1) & " " & Cells(a, 2) & " " & Cells(a, b)
c = c + 1
Next b
Next a
End Sub

run the macro. It will paste the data in sheet2 col A
Ravi

 

[BenjaminDubai]

Errrm

Thank you but I can't get that working. I have the Data in A1, Sheet one.
Nothing appears in sheet 2.

Sorry you may have to assist further!

It looks very neat and nice tho...