help: converting data from column to row

MatthiasK

New Member
Joined
Feb 19, 2010
Messages
3
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

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
Hi Matt, perhaps this code will work for you. Try it on a copy of your data first, of course. :)
Code:
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

New Member
Joined
Feb 19, 2010
Messages
3
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
 

Forum statistics

Threads
1,081,530
Messages
5,359,347
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top