Forum Discussion
Convert list of data to table
Hello everyone,
I have a data set of this form:
| Author | Author1 |
| Title | Title1 |
| Date | Date1 |
| Author | Author2 |
| Title | Title2 |
| Date | Date2 |
| Author | Author3 |
| Title | Title3 |
| Date | Date3 |
And i would like to convert it to a table of that kind:
| Author | Title | Date |
| Author1 | Title1 | Date1 |
| Author2 | Title2 | Date2 |
| Author3 | Title3 | Date3 |
Off course there's more than 3 books to sort 😉
Title1, Author2,... are just sample values for Title, Author,...
Any idea that could help me ?
Thank you so much for your help !
Diego
4 Replies
You may do that with Power Query
- add Index
Integer-divide on 3
Pivot Column2 with All rows of Column3 in aggregation
diegohoutart This should do what you want. It will build a new list in the format you want, starting in cell D1, so make sure D:F are empty.
Sub SplitAuthors() Dim i As Long Dim lr1 As Long Dim lr2 As Long lr1 = Cells(Rows.Count, "B").End(xlUp).Row Range("D1").Value = "Author" Range("E1").Value = "Title" Range("F1").Value = "Date" For i = 1 To lr1 Step 3 lr2 = Cells(Rows.Count, "D").End(xlUp).Offset(1).Row Cells(lr2, "D").Value = Cells(i, "B").Value Cells(lr2, "E").Value = Cells(i + 1, "B").Value Cells(lr2, "F").Value = Cells(i + 2, "B").Value Next i End SubThanks for your help. I have to say that I am a mere beginner. How do I run this code ?
diegohoutart You can right-click any sheet tab, select View Code, which will open the Visual Basic Editor (VBE). Then go to Insert > Module, and paste the code in the new window that opens on the right. Alt+Q will exit you back to Excel. You can run the code by selecting it from the Macros pane (Alt+T+M+M), then press Run. You can also run it from the https://support.office.com/en-us/article/Show-the-Developer-tab-E1192344-5E56-4D45-931B-E5FD9BEA2D45 > Macros.
Sergei - That's a great solution. I had thought about doing it with PQ, but didn't even think of using an Index & divisor.
