Need help with cross reference of parts from more than one source

CROSS REFERENCE.xlsxI have 2 sheets of data that I would like anyone to be able to use to find a series of replacement parts for the one they currently have.
On the first sheet I have the most current part number in column A1 to A242, columns B to R have part numbers in them that include the ones from column A(these are in column B1-B242). The rest of the columns have part numbers for newest to oldest part number used and not all cells are used due to some of the items being new and only cross referencing to column B and other have be cross reference up to 16 or 17 times to get to the newest in column A. The first part of my question would be how could I query the whole table from one cell on sheet 3 and have a column of data appear in order from the row of data on the first sheet on the same sheet(3). the next part would be to have the answer(being the most current part number) to also show on sheet 3 the parts required to make it work. This information would be on sheet 2 and would also have the sheet 1 column A information in the same position but beside these would be 10 more columns with suggested add-on items. If any one can figure this out I would really appreciate that, I am try to show a couple of examples of what I would like the final sheet to look like.


Attachment 293360


Thank you for taking time to even look at this.

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

This thread is active. Duplicate post is when you post the same question in multiple threads / multiple subforums.

Not sure what you are doing, but this may start.

Option Explicit

Sub Macro1()
    Dim SearchText      As String, _
        FoundParm       As Variant, _
        FoundRow        As Long, _
        LastColumn      As Long, _
        PartsArray    As Variant
    
    With Sheets("End Result")
        SearchText = .Range("C3").Value
        With Sheets("Before")
            With .Cells
                Set FoundParm = .Find(What:=SearchText, _
                    After:=ActiveCell, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
            End With '.cells
                
            If Not FoundParm Is Nothing Then
                FoundRow = FoundParm.Row
                LastColumn = .Cells(FoundRow, Columns.Count).End(xlToLeft).Column
                PartsArray = .Range(.Cells(FoundRow, 1), .Cells(FoundRow, LastColumn)).Value
                PartsArray = WorksheetFunction.Transpose(PartsArray)
            End If
        End With    'before sheet
        
        .Range("C7").Resize(Range("c7").CurrentRegion.Rows.Count).Clear
        .Range("c7").Resize(rowsize:=UBound(PartsArray)).Value = PartsArray
    End With    'end result sheet
End Sub

Option Explicit
Sub Macro1()
    Dim SearchText      As String, _
        FoundParm       As Variant, _
        FoundRow        As Long, _
        LastColumn      As Long, _
        PartsArray      As Variant
    
    With Sheets("Lookup")
        SearchText = .Range("C3").Value
        With Sheets("model history")
            With .Cells
                Set FoundParm = .Find(What:=SearchText, _
                    After:=ActiveCell, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
            End With '.cells
                
            If Not FoundParm Is Nothing Then
                FoundRow = FoundParm.Row
                LastColumn = .Cells(FoundRow, Columns.Count).End(xlToLeft).Column
                PartsArray = .Range(.Cells(FoundRow, 1), .Cells(FoundRow, LastColumn)).Value
                PartsArray = WorksheetFunction.Transpose(PartsArray)
            End If
        End With    'model history sheet
        
        .Range("C7").Resize(Range("c7").CurrentRegion.Rows.Count).Clear
        If LastColumn = 1 Then
            .Range("C7").Value = PartsArray
        Else
            .Range("c7").Resize(rowsize:=UBound(PartsArray)).Value = PartsArray
        End If
    End With    'solution sheet
End Sub

Also, there are VLOOKUP formulas in the solution sheet to return the old data.

I have modified the workbook to use a macro to get the unique ids and use the list to fill an activeX combobox for the search. The combobox will filter as you type in the id #. When the box changes the search is triggered automatically.

O.k. This mod will let you type in a value and press Enter. If the value does not exist you will get a pop-up msgbox.