| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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. |
|
#2
|
|||
|
|||
|
Quote:
|
|
#3
|
|||
|
|||
|
Quote:
__________________
Asobi Wa Owari Da |
|
#4
|
|||
|
|||
|
Quote:
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Transpose multiple Columns to rows | Excel Worksheet Functions | |||
| Transpose multiple columns | Excel Discussion (Misc queries) | |||
| Transpose multiple columns, functions | Excel Worksheet Functions | |||
| I need a macro to transpose multiple columns A1-Z1, A2-X2 etc | Excel Discussion (Misc queries) | |||
| repeated transpose from rows to columns with unequal groups | Excel Discussion (Misc queries) | |||
Linear Mode
