Transposing Rows/Columns with Calculations

Maybe :

Sub Test()
  Dim arrIn, arrOut, i As Long, j As Long, k As Long, p As Long

  arrIn = Range("A1").CurrentRegion.Value
  ReDim arrOut(1 To (UBound(arrIn, 1) - 1) * (UBound(arrIn, 2) - 1), 1 To 4)

  k = UBound(arrIn, 2)
  For i = 2 To UBound(arrIn, 1)
      For j = 2 To k - 1
          If arrIn(i, j) <> 0 Then
             p = p + 1
             arrOut(p, 1) = arrIn(i, 1)
             arrOut(p, 2) = arrIn(1, j)
             arrOut(p, 3) = arrIn(i, j)
             arrOut(p, 4) = arrIn(i, j) / 100 * arrIn(i, k)
          End If
      Next j
  Next i

  Range("A11").Resize(p, UBound(arrOut, 2)).Value = arrOut
End Sub

This works perfectly. Thanks a million A couple of quick questions on the code:

1. I've placed ActiveWorkbook.Worksheets.Add after the loop to add the results in a new tab. Where do I set the starting row for the new tab?
2. I've tried moving the Quantity column by increasing the number of materials in the column. Your code works perfectly. So do you look for the last column and take the quantity from there?

Thanks again. Really appreciate this.

You are welcome, thank for the rep. points.

Sub Test()
  Dim arrIn, arrOut, i As Long, j As Long, k As Long, p As Long
  Const TargetCell As String = "B2"

  arrIn = Range("A1").CurrentRegion.Value
  ReDim arrOut(1 To (UBound(arrIn, 1) - 1) * (UBound(arrIn, 2) - 1), 1 To 4)

  k = UBound(arrIn, 2)
  For i = 2 To UBound(arrIn, 1)
      For j = 2 To k - 1
          If arrIn(i, j) <> 0 Then
             p = p + 1
             arrOut(p, 1) = arrIn(i, 1)
             arrOut(p, 2) = arrIn(1, j)
             arrOut(p, 3) = arrIn(i, j)
             arrOut(p, 4) = arrIn(i, j) / 100 * arrIn(i, k)
          End If
      Next j
  Next i

  ActiveWorkbook.Worksheets.Add
  Range(TargetCell).Resize(p, UBound(arrOut, 2)).Value = arrOut
End Sub

- Where do I set the starting row for the new tab?
I set the TargetCell by adding the green lines

- So do you look for the last column and take the quantity from there?
Yes

Excellent! Thanks again for all your help.

You are welcome, glad I can help.


Regards