Search function + results based on results of search

[DStripling]

Hi Excel Masters!

I have a situation here. I've created a search function where I can search a data set by typing in a client name (or partial name), and my table will return all entries with at least a partial match. I.e. search "Ohio" and it returns Ohio State, Ohio State University, Ohio River, etc. That part of the search tool works great. My issue comes in the associated columns of the search results.
The search results can produce duplicates of the client name (Ohio State, Ohio State, etc.) but each client entry (duplicate or not) will have a unique column associated (project names) that I want to populate along side. i.e. [Ohio State - Football Impact] [Ohio State - Baseball Record]
If the project name doesn't contain the search term, it doesn't pull that into the results, causing the results in project name to get misaligned. I believe that is the root of the issue, as I have other columns that work off of the project name column.
The formula I'm using to search the database and pull in the search results (works great):
<code class=" language-markup" style="box-sizing: border-box; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: inherit; padding: 0px; color: inherit; background-color: transparent; border-radius: 0px;">=IFERROR(INDEX(Table3[Client],AGGREGATE(15,6,(ROW(Table3[Client])-ROW(Table3[[#Headers],[Client]]))/ISNUMBER(SEARCH(B$3,Table3[Client])),ROW(A1))),"")</code>B3 is the search box.

Do you guys have any idea how I can get the unique project name to populate along side it's (potential) duplicate client? Here's a screenshot of the problem: each client has a unique project name.

I'm happy to share more info as needed!!

Thanks,
Dylan

 

[mikerickson]

I'm not so good with structured references, but using cell addresses, if your database of Clients is in column X and Project Names are in column Y, put this CSE formula in E3 and drag down


=INDEX(Y:Y, SMALL(IF(X:X=D3,ROW(X:X)), COUNTIF(D$1:D3,D3)), 1)

 

[DStripling]

Thank you! That put me on the right track, I think. But because each client name is not unique, it only returned a project name for the first instance of that client name. That's the tricky part about this :/ Florida International University in row 3 and Florida International University in row 4 are the same client, but would return different project names.

 

[mikerickson]

When in row n, this part (after being dragged down) COUNTIF(D$1:Dn,D) returns the number of instances of that row's Client that are above it. (call that K)

SMALL(IF(X:X=Dn,ROW(X:X)), COUNTIF(D$1:Dn,Dn)) should return the row number of the Kth instance of that row's client in the database.

The INDEX(Y:Y, ....) should return the Project Name for that instance.

 

[DStripling]

Not having any luck with that, as the project name doesn't seem to link to the cell to its left.

 

[Akuini]

How about using VBA?
I may find a way to do that using VBA but I need to know the Table3 layout.

 

[DStripling]

I haven't messed with VBA much.. but the layout for Table3 looks like so:
A:REF
B:Year
C:Code
D:Client Type
E:Client Sub-Type
F:Service
GHidden)
H:Client
I:City
J:State
K:Project Name
L:Project Director
M:Fee

Along with the picture above, I have a few of those other fields out to the right of "Project Name" in the search, but I have the REF and all of that additional data pulling based on what is in the Project Name field. So, in my mind, getting that Project Name field addressed is the #1 issue.

Thanks for the effort!!

 

[Akuini]

So Client is in col H & Project Name in col K.
Ok, say Table3 is in sheet2 and you do the search in sheet1.
Put this code in the code module of the sheet1, like this:
Copy the code then right click sheet1 tab > select View Code > paste the code.
The code use Worksheet_Change targeting cell B3, it means if the focus is in cell B3 then you exit the cell then the code will be triggered.
So type something in B3 then exit.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Range("B3")) Is Nothing Then
    Dim i As Long, k As Long, va
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Range("D3:E" & Cells(Rows.Count, "D").End(xlUp).Row).ClearContents
        ReDim va(1 To 100000, 1 To 2)
        
        For Each r In [COLOR=#0000ff]Sheets("Sheet2")[/COLOR].ListObjects("Table3").ListColumns("Client").DataBodyRange
            If InStr(1, r.Value, Target.Value, vbTextCompare) Then
            k = k + 1
            va(k, 1) = r.Value: va(k, 2) = r.Offset(, 3).Value
            End If
        Next
        
        If k > 0 Then Range("D3").Resize(k, 2) = va
        
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        
    End If
End Sub