help: converting data from column to row

[MatthiasK]

Hi,

I want to analyse bibliographical data with excel and I need to split up data from one cell (author names, seperated by semicolon) and then convert it into new rows. Then I have to populate the data from the other cells to the new rows.

Here is a screenshot of the data:

...and this is how the data from the first row should look like after the conversion:

I would be grateful for any help!
Thanks a lot!

Matt

 

[mvptomlinson]

Hi Matt, perhaps this code will work for you. Try it on a copy of your data first, of course.

Sub expand()
Dim i As Long, rSize As Long
For i = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    With Cells(i, 1)
        If InStr(.Value, ";") > 0 Then
            rSplit = Split(.Value, ";")
            rSize = Len(.Value) - Len(Replace(.Value, ";", ""))
            .Offset(1, 0).Resize(rSize, 1).EntireRow.Insert
            .Offset(0, 1).Resize(rSize + 1, 3).FillDown
            For j = 0 To rSize
                Cells(i + j, 1).Value = rSplit(j)
            Next j
        End If
    End With
Next i
End Sub

 

[MatthiasK]

Dear Tom,
thanks so much! It works great and I managed to adjust the code a bit for other data, too.
Thanks again!
Greetings
Matt