I've just realized "Unit Information".
This will automate everything as long as you merge the cells under the same logic.
1) 
Private Sub ConvertData_Click()
    Dim e, msg As String, FixedCols
    With Sheets("MasterData")
        FixedCols = Application.Match("unit information", .Rows(1), 0)
        If IsNumeric(FixedCols) Then
            FixedCols = .Cells(1, FixedCols).MergeArea.Cells.Count
        Else
            MsgBox "Nit Information is missing": Exit Sub
        End If
    End With
    For Each e In Array(Array("PREVENTATIVE ACTIONS (TYCOM or CMD Specific)", "Alpha"), _
                        Array("Whatever the actual category name a", "Bravo"), _
                        Array("Whatever the actual category name b", "Charlie"))
        test e(0), e(1), FixedCols, msg
    Next
    If Len(msg) Then MsgBox msg
End Sub
2)

Sub test(ByVal myCategory As String, ByVal wsName As String, FixedCols, msg As String)
    Dim a, b, i As Long, ii As Long, iii As Long, n As Long, myCols
    With Sheets("MasterData")
        myCols = Application.Match(myCategory, .Rows(1), 0)
        If IsNumeric(myCols) Then
            With .Cells(1, myCols).MergeArea
                myCols = Array(.Column, .Column + .Columns.Count - 1)
            End With
        End If
        a = .Cells(1).CurrentRegion.Value
    End With
    If Not IsArray(myCols) Then msg = msg & vbLf & myCategory & " title not found": Exit Sub
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 9)
    For i = 3 To UBound(a, 1)
        For ii = myCols(0) To myCols(1)
            n = n + 1
            For iii = 1 To FixedCols
                b(n, iii) = a(i, iii)
            Next
            b(n, 8) = a(2, ii): b(n, 9) = a(i, ii)
    Next ii, i
    With Sheets("ConvertedData_" & wsName)
        .Cells.Delete
        .Cells(1).Resize(, FixedCols).Value = Application.Index(a, 2, 0)
        .Cells(1, FixedCols + 1).Resize(, 2) = Array(wsName, "Value")
        .[a2].Resize(n, 9).Value = b
        .ListObjects.Add(1, .Cells(1).CurrentRegion).Name = "Table_ConvertedData_" & myCategory
    End With
End Sub