Please! ... I need your Help!!

[tazmtiger]

I am not sure how I can accomplish the following task? …

I am currently working with two worksheets from same workbook “Sheet1 & Sheet2.”
In Sheet1 I utilize LOOKUP to extract matching values from within same Sheet1.
These values are pulled from each corresponding column through the use of the LOOKUP function, returning corresponding values based on criteria in Cell D8 (ROW 8) Columns D, E, F, G, H & I, values pulled from a range starting at “D13:i2000” in Sheet1.

On the second worksheet, (Sheet2) I use a VBA code to filter specific values found within Sheet2 Column (E) starting at range E6:E20000, returning filtered value to cell E3.

Note: The information contained in Sheet2, Column E, range E6:E20000 is unique and does not repeat twice.
The information found in Sheet1, Columns D, E, F, G, H & I. is the same as the information contained in Sheet2 Column E, with the exception that in Sheet1 “it does repeat many times” through the entire workbook. But. Only within the same columns, not the same rows.

Sheet1 is utilized to enter job specs, on each row to identify “SPECIFIC PART NEEDS” for each JOB ID Number.
Again: No part number is repeated in the same ROW, but it those part ID numbers can be found within the same Columns D, E, F, G, H & in the entire length of the spreadsheet.

On Sheet2 Column (E) the same list of part numbers, can be found, but only contains one occurrence for each value within that column (E).

On Column (F) Sheet2, there is a list “Rack bin numbers” range F6:F20000, to identify the specific location of each of the part numbers that is found on Column (E).

Here is what I would like it to do:

In Sheet1 Columns D, E, F, G, H & I, Which is where it displays, or shows the LOOKUP values corresponding to the selected “JOB ID Number”. I would like it to display on ROW 9 on Sheet1corresponding Columns D, E, F, G, H & I… A BIN Location numbers, which is found on Sheet2 column F.

So basically; it needs to find a MATCH on “Column E’s range” (Sheet2), from each of values within Sheet1 Columns D, E, F, G, H & I LOOKUP values, and copy those values from the cells to the right of the matched cell, which would be cells from Column (F) on Sheet2 and display those cell values underneath each matched value in Columns D, E, F, G, H & I.

Example:
If Sheet1, Row 8, cell (D8) has a value = 123456…. Look and find that “123456” value in Column (E)’s entire range in Sheet 2, and when it finds a that MATCH on Sheet2, Column (E), COPY THE VALUE FROM THE CELL TO THE RIGHT OF THAT MATCHING CELL, which contains the Bin location Number found on Column (F) Sheet2. Place that found value underneath the corresponding 123456 value in Sheet1.

Repeat the same process for each of the LOOKUP values from within Columns D, E, F, G, H & I LOOKUP ROW8 in Sheet1. (Placing the corresponding value underneath each matched LOOKUP VALUE in Sheet1.

I know this is probably very confusing, but I can’t seem to get it right.
I need someone to aid me, or help me through this process, by utilizing a VBA code, or cell formula string.
Please Help! Thank you so much in advance, for any consideration and or, time given to my request.

Thank You!

 

[JLGWhiz]

and copy those values from the cells to the right of the matched cell, which would be cells from Column (F) on Sheet2 and display those cell values underneath each matched value in Columns D, E, F, G, H & I.

Do you currently have empty rows beneath each job ID row which has data in D:I? Or would rows have to be inserted after each job ID row?

 

[JLGWhiz]

If you already have the empty rows, just comment out the If...Then statement whrere it inserts a row. Give this a try.

Sub getLocation()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, i As Long, lr As Long
Set sh1 = Sheets("Sheet1") 'edit sheet name
Set sh2 = Sheets("Sheet2") 'edit sheet name
lr = sh1.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
    For i = lr To 13 Step -1
        For j = 4 To 9
            Set fn = sh2.Range("E:E").Find(sh1.Cells(i, j).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    If sh1.Cells(i + 1, j) <> "" Then
                        sh1.Rows(i + 1).Insert
                    End If
                    sh1.Cells(i + 1, j) = fn.Offset(, 1).Value
                End If
        Next
    Next
End Sub