# How to convert one row of data into multiple rows in Excel

How do I take one row of data in Excel and split it into multiple rows?

Given this input:

Name      Award Type 1     Amount 1     Award Type 2    Amount 2
Dan       Top Performer    $5,000 Top Region$10,000
Kathy     Rep Planmaker    $2,500  I want to convert it to this: Name Award Type Amount Dan Top Performer$5,000
Dan       Top Region       $10,000 Kathy Rep Planmaker$2,500


In words: since the “Dan” row in the input data shows two awards – one in columns B&C and another in columns D&E – I want to split Dan’s data into two rows, one for each award.

-
Please edit your question to show cells you already have, and cells you would like. – DaveP Jul 30 '12 at 15:37
possible duplicate of How do I split one row into multiple rows with Excel? – G-Man Sep 5 '14 at 19:40
Also related: superuser.com/q/789075/76571 – Excellll Sep 5 '14 at 19:46

Sub convert()
Dim intcount As Integer
intcount = 1
For i = 2 To **123**
For j = 0 To 1
If ThisWorkbook.Sheets("sheet1").Cells(i, 1 + 2 * j).Value <> vbNullString Then
intcount = intcount + 1
ThisWorkbook.Sheets("sheet1").Cells(i, 1).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 1)
ThisWorkbook.Sheets("sheet1").Cells(i, 2 + 2 * j).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 2)
ThisWorkbook.Sheets("sheet1").Cells(i, 3 + 2 * j).Copy Destination:=ThisWorkbook.Sheets("sheet2").Cells(intcount, 3)
End If
Next
Next
End Sub

-