Split cells in a list to create a longer list

[richardallen]

Grateful with some help on a vba task. I am looking to use convert this list:

ABC21256/03/04
ABC21311/01/09/11
ABC21313/02
ABC21378/07/08/31

into to this list:

ABC21256/03
ABC21256/04
ABC21311/01
ABC21311/09
ABC21311/11
ABC21313/02
ABC21378/07
ABC21378/08
ABC21378/31

i.e. each line is split after the "/" and concatenated with the starting string (before the first "/") to create a new sequence and put on separate rows.

 

[Rick Rothstein]

What would I change to have the data lists start from row 2 (so I can add titles for the user)?

For the code I posted in Message #6 , you would change the A1 to A2 on the code line assigning to the Addr variable and change the B1 to B2 on the last code line.

 

[Rick Rothstein]

Brilliant, thanks. Finally, what would the code be to reverse this? i.e. reassemble a dis-aggregated list:

From:
ABC21256/03
ABC21256/04
ABC21311/01
ABC21311/09
ABC21311/11

To:
ABC21256/03/04
ABC21311/01/09/11

Here is my non-looping method to do this (it is the complement to the non-looping code I posted to your original question in Message #6 ). Note that I assumed your data started at cell A2 with A1 containing header text... if that is the wrong starting cell, simply change the A2 to what the actual starting cell is for you data.

[table="width: 500"]
[tr]
	[td]Sub ReassembleWithSlashes()
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Copy .Offset(, 1)
    .Offset(, 1) = Evaluate(Replace(Replace("IF(LEFT(@,FIND(""/"",@)-1)=LEFT(#,FIND(""/"",#&""/"")-1),REPLACE(@,1,FIND(""/"",@)-1,""""),@)", "@", .Offset(, 1).Address), "#", .Offset(-1, 1).Address))
    With .Offset(, 1)
      .Value = Application.Transpose(Split(Replace(Join(Application.Transpose(.Cells), Chr(1)), Chr(1) & "/", "/"), Chr(1)))
      .SpecialCells(xlConstants, xlErrors).Clear
    End With
  End With
End Sub[/td]
[/tr]
[/table]