D4

Option Explicit


Sub compact()
    Dim wsdt As Worksheet, wsmt As Worksheet
    Dim raw_data As Range, master_bom As Range, index As Range

    Dim a As Long, b As Long, x As Long, y As Long
    Dim k As Long, i As Long, j As Long

    Set raw_data = Application.InputBox(prompt:="Select your range", _
    Title:="Input data", _
    Type:=8)

    Set master_bom = Application.InputBox(prompt:="Select your master bom range", _
    Title:="Input data", _
    Type:=8)

    x = raw_data.Rows.Count
    y = raw_data.Columns.Count

    a = master_bom.Rows.Count
    b = master_bom.Columns.Count

    Set wsdt = raw_data.Worksheet
    Set wsmt = master_bom.Worksheet

    For i = 1 To y
        For j = 1 To x
            With Worksheets(wsmt.Name)
                Set index = master_bom.Find( _
                What:=raw_data(j, i), _
                LookIn:=xlValues)
                raw_data(j, i).Offset(0, -1) = master_bom(index.row, 1)
                'Debug.Print (raw_data(j, i) & "Found in row" & index.row)
            End With
        Next j
    Next i



End Sub