How to take data from columns in some rows and insert in new row?

I have got a VBA code for you.

Option Explicit

Sub convert_data()
Dim lrow As Long
Dim lcol As Long
Dim i As Long
Dim a As Long
Dim j As Long

a = 1
With Worksheets("Sheet1")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 1 To lrow
        lcol = .Range("IV" & i).End(xlToLeft).Column
        For j = 2 To lcol
            If lcol > 2 Then
                Worksheets("Sheet2").Cells(a, 1).Value = .Cells(i, 1).Value
                Worksheets("Sheet2").Cells(a, 2).Value = .Cells(i, j).Value
                a = a + 1
            Else
                Worksheets("Sheet2").Cells(a, 1).Value = .Cells(i, 1).Value
                Worksheets("Sheet2").Cells(a, 2).Value = .Cells(i, j).Value
                a = a + 1
                Exit For
            End If
        Next j
    Next i
End With

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.

This seems to work:

Insert this macro into a new module and run the macro CleanData

Sub CleanData()
Dim r As Range
Dim i As Long
Dim a As String, c As String

Set r = Range("A1")

'Starting at first row data loop through till column A cell is blank
For i = 0 To 65536  'loop to some arbitrary ridicously high number
   If r.Offset(i, 0).Value = "" Then
      Exit For 'no more rows to process
   End If
   If r.Offset(i, 2).Value <> "" Then 'We have to copy this as a new row
      'fill variables with what we want to copy
      a = r.Offset(i, 0)
      c = r.Offset(i, 2)
      'insert a new row below current
      r.Offset(i + 1, 0).EntireRow.Insert
      'copy values to new row
      r.Offset(i + 1, 0).Value = a
      r.Offset(i + 1, 1).Value = c
      'clear c column value
      r.Offset(i, 2).Value = ""
   End If
Next
End Sub

What about columns B-I and M-Q do they need to be copied down as well?

Does the data in J-L need to be cleared?

A small sample workbook with the raw data and another sheet of what you want it to look like would be of great help in assisting you.

Took a guess on what you wanted. The code is not very efficient but works. I attached a work book with the macro ...
FunkyCopyDown.xlsm

Sub CleanData()
Dim r As Range
Dim i As Integer, j As Long

Set r = Range("A1")

'Starting at first row data loop through till column A cell is blank
For j = 0 To 65536  'loop to some arbitrary ridicously high number
   If r.Offset(j, 0).Value = "" Then
      Exit For 'no more rows to process
   End If
   i = 0
   If r.Offset(j, 9).Value <> "" Then 'something in J
      i = i + 1
      InsertNewRow Range(r.Offset(j, 0).Address), 10, r.Offset(j, 9).Value, i
   End If
   If r.Offset(j, 10).Value <> "" Then 'something in K
      i = i + 1
      InsertNewRow Range(r.Offset(j, 0).Address), 11, r.Offset(j, 10).Value, i
   End If
   If r.Offset(j, 11).Value <> "" Then 'Something in L
      i = i + 1
      InsertNewRow Range(r.Offset(j, 0).Address), 12, r.Offset(j, 11).Value, i
   End If

Next
End Sub

Sub InsertNewRow(therange As Range, col As String, val As String, rT As Integer)
'col will be the col number  ie J = col 10, K=11, L=12
'val will be the associated val for the column
'rT will be the number of inserted rows

'Insert Row
therange.Offset(rT, 0).EntireRow.Insert
'Copy the value of cell A above it down
therange.Offset(rT, 0).Value = therange.Offset(0, 0).Value
'Copy the value j,k,or L to Cell I on the new row
therange.Offset(rT, 8).Value = val
'clear the value above where it came from
therange.Offset(0, col - 1).Value = ""
End Sub

I am glad.... just bear in mind that I took a brute force approach to your problem. The code is done without any finesse or thought of efficiency.

Enjoy!