I'm assuming all your data is stored in Sheet1 as per table in the attachment, only starting from the top most cell i.e. A1.
("RM1" is in Cell A3, "RM2" in cell A4, and so on)
Run the following macro when data has been set:
Your results will be in Sheet2.
Sub BillOfMaterial()
counter = 1
'nCol refers to product columns from column B to column E i.e. 2nd column to the 5th column
For nCol = 2 To 5
j = 2
Sheets(2).Cells(1, counter).Value = Sheets(1).Cells(2, nCol).Value
'Check raw material amount per product
For i = 3 To Sheets(1).UsedRange.Count
If Sheets(1).Cells(i, nCol).Value = "" Then
'Do Nothing
Else
Sheets(2).Cells(j, counter).Value = Sheets(1).Cells(i, 1).Value
Sheets(2).Cells(j, counter + 1).Value = Sheets(1).Cells(i, nCol).Value
j = j + 1
End If
Next
Cells(1, counter).Select
ActiveCell.Resize(, 2).Select
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
counter = counter + 2
Next
End Sub
Bookmarks