ExcelBanter - Transpose multiple groups of columns to multiple rowsrow

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Transpose multiple groups of columns to multiple rowsrow (https://www.excelbanter.com/excel-worksheet-functions/268041-transpose-multiple-groups-columns-multiple-rowsrow.html)

Transpose multiple groups of columns to multiple rowsrow

I have on 1 row:

Date, Author, URL, Book1, URL1, Sales1, Book2, URL2, Sales2, Book3, URL3, Sales3.

And repeat.

I would like:

Date, Author, URL, Book1, URL1, Sales1,
Date, Author, URL, Book2, URL2, Sales2,
Date, Author, URL, Book3, URL3, Sales3,

Thank you for your help.

Quote:

Originally Posted by Billy_McSkintos (Post 961966)

Bumping as I really need a solution. I hope someone can help?

Quote:

Originally Posted by Billy_McSkintos (Post 961966)

Pivot table?

Quote:

Originally Posted by wickedchew (Post 962574)

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