Quote:
Originally Posted by wickedchew
Pivot table?
|
Thank you wickedchew. I managed to do it after posting in the Friday "Open" thread on
http://lifehacker.com/tag/openthread/. @Enginerd replied with some VBA:
Code:
Sub modify_data()
'source data is on Sheet1
'parsed data is on Sheet2
DestinationRow = 2 'initial destination row
For i = 1 To 5 'define how many rows you need to segregate
j = 4 'defines the starting column of the first book title
Do Until Sheet1.Cells(i, j).Value = "" 'do until there is no book title
Sheet2.Cells(DestinationRow, 1).Value = Sheet1.Cells(i, 1).Value 'Date
Sheet2.Cells(DestinationRow, 2).Value = Sheet1.Cells(i, 2).Value 'Author
Sheet2.Cells(DestinationRow, 3).Value = Sheet1.Cells(i, 3).Value 'URL
Sheet2.Cells(DestinationRow, 4).Value = Sheet1.Cells(i, j).Value 'Book
Sheet2.Cells(DestinationRow, 5).Value = Sheet1.Cells(i, j + 1).Value 'URL
Sheet2.Cells(DestinationRow, 6).Value = Sheet1.Cells(i, j + 2).Value 'Sales
DestinationRow = DestinationRow + 1 'increment the row for the next entry
j = j + 3 'change column reference to the column containing the next possible book title in the source row
Loop
Next i
End Sub
With a little tweaking for my specific situation this worked perfectly! I hope this helps someone else but the thanks must go to @Enginerd