Workbook search and display

[JimLahey]

Hello,

I am looking how to accomplish: Search a same horizontal column on every sheet in workbook(same column (C2:C36) on each sheet) by entering keyword in a cell and display in a horizontal row (array) or series if multiple keywords are found display multiple horizontal rows (arrays)

Inventory workbook with: row (A1:AH) BIN, IMAGE, P/N, QTY., UoM, DESCRIPTION, PART TYPE

I am looking to search the P/N column for specific part number by entering a part number(string of numbers and or letters) and displaying a horizontal row of the information found in that row of worksheet AND if there are multiple P/N's found display those rows(table?) below in say, up to 10 items.

I am also wanting to do the same for the DESCRIPTION column with ability to use wild card search, such as: one or more keywords separated by "+" symbol and return row information that is found, if multiple descriptions are found, display those rows consecutively(table?) up to 10 items.

Any ideas where to start?

Thank you.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

display those rows

Please describe where those rows are to be displayed, at the bottom of the same sheet or another sheet. Also, in which cell will you be entering the keyword?

 

[JimLahey]

I am looking to use search boxes A (by Part Number) or B (by Part Description)

I am looking to display result(s) in table C

screenshot for reference:

temp.png

Shared with Dropbox

www.dropbox.com

Thanks.

 

[mumps]

Thank you for the upload. However, it is hard to work with a picture. Please upload a copy of your file. Also, you have 6 columns in the "table C". Only columns K, L, M and O in the table can be populated with data from your main table. Is that what you want? How do you decide whether to search by part number or description?

 

[JimLahey]

Thank you for the upload. However, it is hard to work with a picture. Please upload a copy of your file. Also, you have 6 columns in the "table C". Only columns K, L, M and O in the table can be populated with data from your main table. Is that what you want? How do you decide whether to search by part number or description?

Yes I'm only looking at populating certain columns from inventory list into display table C.

I want to allow either entries A or B for part search.

Basically I am trying to work in a part lookup either by part number or string description (words do not necessarily need to been in correct order (this was my wildcard option)) (say someone searches for nut+wing or wing+nut) for description search, I want to populate table C with relative rows of column info, and if they type in "wing+nut+1/2" in any order it will display half inch wing nuts. Also I want to truncate the results at top 10 by keyword, so if the search result comes back with 20 hits only show first 10 until some keys in at B more determining keywords.

For the part number, I may have two different bins with same part number. Thats why I want to have a table C of hits.

Thanks.

I will try to get a copy sent out when I can.

Thank you.

 

[JimLahey]

Here is an uploaded copy of workbook in progress. I still have more sheets to add. I would like the search function part on its own sheet, but be able to look through each sheet (BIN A, BIN B, BIN C, etc) data for possible hits in Part Number and Description (column C and column F).

Thank you for your assistance.

INVENTORY LOG.temp.xlsx

Shared with Dropbox

www.dropbox.com

 

[mumps]

I assume that you want to search by either part number or description. Is this correct? Also, there are two approaches that we can take. The first is to enter a part number or a description and run the macro manually to do what you want. The second approach is when you enter a part number or description, and press the RETURN key or TAB key, the macro would run automatically. Which approach would work better for you?

 

[mumps]

Click here to download your file. This file takes the second approach I described previously. It will allow you to enter only one of Part Number or Description. Enter a Part number in L5 and press the RETURN key or TAB key to search by Pat Number. Enter a Description in L6, each criteria separated by a + sign with no spaces (wing+nut+1/2") and press the RETURN key or TAB key to search by Description. Please let me know how it works out.
This is the code in the worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L5:L6")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim PN As Range, ws As Worksheet, descr As Variant, i As Long, ii As Long, cnt As Long, splitD As Variant
    If Target <> "" Then
        Select Case Target.Row
            Case Is = 5
                For Each ws In Sheets
                    Set PN = ws.Range("C:C").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
                    If Not PN Is Nothing Then
                        Cells(Rows.Count, "K").End(xlUp).Offset(1) = PN
                        Cells(Rows.Count, "L").End(xlUp).Offset(1) = PN.Offset(, 3)
                        Cells(Rows.Count, "N").End(xlUp).Offset(1) = PN.Offset(, 2)
                        Cells(Rows.Count, "O").End(xlUp).Offset(1) = PN.Offset(, 5)
                    End If
                Next ws
            Case Is = 6
                For Each ws In Sheets
                    descr = ws.Range("C2", ws.Range("C" & ws.Rows.Count).End(xlUp)).Resize(, 6).Value
                    For i = LBound(descr) To UBound(descr)
                        splitD = Split(Target, "+")
                        For ii = LBound(splitD) To UBound(splitD)
                            If InStr(descr(i, 4), splitD(ii)) Then
                                cnt = cnt + 1
                            End If
                        Next ii
                        If cnt = UBound(splitD) + 1 Then
                            Cells(Rows.Count, "K").End(xlUp).Offset(1) = descr(i, 1)
                            Cells(Rows.Count, "L").End(xlUp).Offset(1) = descr(i, 4)
                            Cells(Rows.Count, "N").End(xlUp).Offset(1) = descr(i, 3)
                            Cells(Rows.Count, "O").End(xlUp).Offset(1) = descr(i, 6)
                        End If
                        cnt = 0
                    Next i
                Next ws
        End Select
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("L5:L6")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Row
        Case Is = 5
            Target.Offset(1).ClearContents
        Case Is = 6
            Target.Offset(-1).ClearContents
    End Select
    Application.EnableEvents = True
End Sub

 

[JimLahey]

THANK YOU!!!

This is so great functionality.

I am trying to understand the code used. I have some C+ experience and very old BASIC from Tandy Radio Shack days.

A couple of things maybe we could do:

- Is there a way to display multiple hits in table C if there are two same part numbers are found in workbook? It only stops at first part number hit.

- Can we display the Qty. (D column) in table C, QoH (M column) when items are found in L5 or L6 search?

- Is there a key stroke macro that we can use for clearing table C (something like ctrl-DEL? ctrl+D or ctrl+shift+"" )?

- What lines / how would I, add to code to added work sheets, say BIN C, BID D, etc?

Thank you so much for your wisdom!

 

[JimLahey]

I apologize for the hassle. I added BIN column to table C to reflect bin location of part number/description hits.

Can we add this to display information of column A of hit to column J?

Thank you so much mumps! your the best!

updated file shared

JimLahey.xlsm

Shared with Dropbox

www.dropbox.com