VBA Macro to combine two row data into one row

[cbacba]

Hello,
I am really struggling trying to figure out how to take data from two rows (from Sample File 1) and combining into single rows (Sample File Result).

Most of the sets are two row combinations that can be combined into one.
However, there are a few that the first line will have multiple rows underneath, that will need to be moved, then the data from the first row copied down to match when the multiple rows are moved. I've highlighted examples of that in gray on the result image.

Thanks for the help!

 

[iggydarsa]

Option Explicit
Sub TransferData()
    'Assuming the data is being transfered from "Sheet1" to "Sheet2"... You might want to change the names in the code if you are using other names.
    Dim srcRowStart As Long: srcRowStart = 9 'The row on the main sheet where the starts
    Dim srcRowEnd As Long
    Dim destRowStart As Long: destRowStart = 1 'Change this to "destRowStart = 2" id you have a header on the first row of Sheet2
    Dim destRowCounter As Long: destRowCounter = destRowStart
    Dim prevRowIsBlank As Boolean: prevRowIsBlank = True
    Dim firstRange As Range
    Dim secondRange As Range
    Dim i As Long
    
    srcRowEnd = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    For i = srcRowStart To srcRowEnd
        If prevRowIsBlank Then
            If Worksheets("Sheet1").Range("A" & i).Value <> "" Then
                Set firstRange = Worksheets("Sheet1").Range("A" & i & ":D" & i)
                prevRowIsBlank = False
            End If
        Else
            Set secondRange = Worksheets("Sheet1").Range("A" & i & ":J" & i)
            Worksheets("Sheet2").Range("A" & destRowCounter & ":D" & destRowCounter).Value = firstRange.Value
            Worksheets("Sheet2").Range("E" & destRowCounter & ":N" & destRowCounter).Value = secondRange.Value
            destRowCounter = destRowCounter + 1
        End If
        If Worksheets("Sheet1").Range("A" & i + 1).Value = "" Then prevRowIsBlank = True
    Next i
    
End Sub