Dear Experts


I am having vertical Bill of Materials (BOMs) of my 1000s of items in Columnar form. In need to convert it into rowwise format BOMs.



The source BOM and desired output is shown in the attached excel file.


Can somebody help my achieving the above desired output?


Thanks for your help in advance.

The above query also posted on following forum:-


https://social.technet.microsoft.com...ba?forum=excel

Attached Files

1-BomTranspose Macro - Forum.xlsx (11.2 KB, 11 views)

Dear Experts,

With respect to my previous post I have made a Macro as follows which is working fine:-

Sub TransposeBom()
Option Explicit
Dim SrcRng As Range 'Source Range
Dim TrgRng As Range 'Target Range
Dim i As Integer, SrcNoofRecords As Integer, r As Integer
Dim RowOffset As Integer, Columnoffset As Integer
Dim PrevParentCode As String, CurrParentCode As String
Dim PrevParentCellAdd As Range
Dim CurrParentCellAdd As Range
Dim PrevRow, PrevCol As Integer


RowOffset = 0
Columnoffset = 0
c = 0
i = 1
j = 0

With ActiveSheet
Set SrcRng = Application.InputBox("Select a Source Range from Where Datat is to be Copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & SrcRng.Address)
Set TrgRng = Application.InputBox("Select a Cell in which the data is to be copied", "Obtain Range Object", Type:=8)
MsgBox ("The Cells Selected were " & TrgRng.Address)

End With

r1 = SrcRng(1).Row - 1

c1 = SrcRng(1).Column - 1
PrevParentCode = SrcRng(1).Value
R2 = r1 + 1

MsgBox (" The rows " & SrcRng.Rows.Count & " and the Columns " & SrcRng.Columns.Count)



For Each CELL In SrcRng.Cells


TrgRng.Cells.Offset(RowOffset, Columnoffset).Value = CELL.Value


c = CELL.Column
If c = 9 Then
If SrcRng.Cells(r1, 1) = SrcRng.Cells(R2, 1) Then
Columnoffset = Columnoffset + 1
r1 = r1 + 1
R2 = R2 + 1
'
Else
Columnoffset = 0
RowOffset = RowOffset + 1
r1 = r1 + 1
R2 = R2 + 1
End If
Else
Columnoffset = Columnoffset + 1
End If



Next CELL
End Sub


My source data was as per attached image:-



I am getting the output as shown in attached image by executing the above Macro:-





the only addition in above program needed is avoiding the repetition of Parent Items highlighted in red in above image. Any sort of help in above regard will be great help to me. Thanks in advance.

best regards

Attached Images

	1-BomTranspose Macro - Forum - Source Data.png (21.0 KB, 15 views)
	1-BomTranspose Macro - Forum - Changes in Output Data_001.png (141.8 KB, 15 views)

Based on your original post and attached file.
You only need to use column B data as your source range.

Attached Files

1-BomTranspose Macro - Forum.xlsm (19.7 KB, 9 views)

Wow! Thanks a great help to me!!

Thanks. But how to mark it as an answer

From the Thread Tools drop down, on the line above your first post, you select "Mark this thread as solved"

You should also go to your other posting of this question and put a link to here.