Wanting multiple rows of results from single cell search

[mikemathis]

I'm using Office 2019 and have a sheet ~1000 rows of Cemetery data

A

B

C

D

E

F

G

H

Plot #

Row

Last Name

First Name

Birth

Death

Inscription

Stone Condition

I want to do a search on Last Name and get multiple rows of results. e.g. If I search for "Smith" and there are 5 different Smiths buried, I'd like the result to be 5 separate rows.

Many thanks for any guidance.
Mike

 

[vcoolio]

Hello Mike,

You could use a Worksheet_Change event code to filter on the required surname as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False
        
        With Me.[A1].CurrentRegion
                .AutoFilter 3, Target.Value
        End With

Application.ScreenUpdating = True

End Sub

This assumes that you have a search cell (J1 in this case) in which a surname is entered. Once you click away from the cell, the surname will be filtered and all relevant rows of data will be displayed for that surname.

To implement this code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

 

[mikemathis]

Hello Mike,

You could use a Worksheet_Change event code to filter on the required surname as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False
       
        With Me.[A1].CurrentRegion
                .AutoFilter 3, Target.Value
        End With

Application.ScreenUpdating = True

End Sub

This assumes that you have a search cell (J1 in this case) in which a surname is entered. Once you click away from the cell, the surname will be filtered and all relevant rows of data will be displayed for that surname.

To implement this code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

I hope that this helps.

Cheerio,
vcoolio.

Thanks Vcoolio. I've never done VBA, but this is a good reason to give it a try. Stay tuned.
Mike

 

[Fluff]

I'm using Office 2019

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[mikemathis]

Vcoolio, did exactly as above. When I click away from the "Last Name" I get a 'filtered' list (with "Available" as the filter) but no results.

Ultimately, I'd love to have the results in a separate tab - or run the query from a different tab that queries the tab named "Details".

 

[vcoolio]

Hello Mike,

I'm not sure where 'available' comes from as, according to the information you supplied, you intended to filter on a selected surname from Column C.
So that we can see exactly what's going on, please upload a sample of your workbook to a free file sharing site such as Drop Box or WeTransfer then post the link to your file back here. Alternatively, you can use the XL2BB tool to upload a sample (see the tool selector at the top of any reply box). Please ensure that the sample is an exact replica of your actual workbook and if you have any sensitive data, then please use dummy data.

In the meantime, have a look at the mock-up file that I've attached here. It's how I've envisioned your workbook to be set out but this time I've added to the code so that the filtered data is transferred to another sheet (Sheet2). In the yellow coloured cell (J1), I've placed a drop down list of surnames from Column C in the data. Simply select a name from the list to execute the code. The amended code is as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("J1")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Application.ScreenUpdating = False

        Sheet2.UsedRange.Offset(1).Clear
        
        With Me.[A1].CurrentRegion
                .AutoFilter 3, Target.Value
                .Offset(1).EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
                Sheet2.Columns.AutoFit
                
Application.ScreenUpdating = True

End Sub

It still needs to be placed into the worksheet module of your input sheet as described in my last post.

There are a couple of other ways that this can be done but try the above method first then we'll go from there.

Cheerio,
vcoolio.

 

[mikemathis]

You are absolutely right. Works perfectly. Thank you for such a straightforward solution.

 

[vcoolio]

You're welcome Mike. I'm glad to have been able to assist.

Cheerio,
vcoolio.