View Single Post
 
Old 05-20-2018, 09:05 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 814
NoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the roughNoSparks is a jewel in the rough
Default

Based on your original post and attached file.
You only need to use column B data as your source range.
Code:
Sub TransposeBom()
    Dim lr As Long, rng As Range, cel As Range
    Dim ChildNum As Integer
    Dim destSheet As Worksheet, writeRow As Long
    
With Sheets("Source or Input")
    lr = .Range("B" & Rows.Count).End(xlUp).Row
    Set rng = .Range("B2:B" & lr)
End With

Set destSheet = Sheets("Desired Output")
writeRow = 5   'to keep away from posted desired results

For Each cel In rng
    If cel.Value <> cel.Offset(-1).Value Then
        ChildNum = 1
    Else
        ChildNum = ChildNum + 1
    End If
    
    With destSheet
        If ChildNum = 1 Then
            writeRow = writeRow + 1
            .Cells(writeRow, 1).Resize(, 3).Value = cel.Resize(, 3).Value
            .Cells(writeRow, 4).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
        Else
            .Cells(writeRow, 4).Offset(, (ChildNum - 1) * 5).Resize(, 5).Value = cel.Offset(, 3).Resize(, 5).Value
        End If
    End With
Next cel
End Sub
Attached Files
File Type: xlsm 1-BomTranspose Macro - Forum.xlsm (19.7 KB, 3 views)
Reply With Quote