Search worksheets and display items on another worksheet

[Lourry]

Hi,

I have a large excel workbook consisting of 2 worksheets (which later on we may add more). Is there a way to have a search toolbar on the first worksheet letting the user type in a search string that will query all the rows in data worksheets and display it in the "results" worksheet?

Worksheet1("Main"): search toolbar with a search button that lets the user click it and starts query. If it finds the string in any row(s) in the data worksheets then it will copy the entire row(s) to the "results" worksheet

Worksheet2("results"): contains all the rows that have the search string entered by the user in the first worksheet

WOrksheet3("DataABC"): data worksheet

Worksheet4("DataXYZ"): data worksheet

I know this will probably be easier to do in Access, however, my supervisor would like to keep it within excel.

I need all the help I can get. Thanks in advance and all the effort.

Thanks again,
Lory

 

[Lourry]

Hi,

Just want to clarify that the search toolbar is basically a cell where the user types in the "string" they want to search for in the data worksheets within the workbook.

Thanks again,
Lory

 

[Loomah]

are you looking for your search data anywhere in your data set or just a specific field?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

hi
the following takes any data found in any field as a result, clears previous results and pastes as required.

there are likely to be many possible solutions to this problem (it may be possible using pivot tables and is likely to be possible using ms query) so this isn't a definitive solution.

i've used the worksheet change event to fire the code, entering the search criteria in cell a5. the code resides in the "main" worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim c As Range
Dim nr As Long 'new row
Dim firstAddress As String

If Target.Address = "$A$5" Then
    nr = 0
    'clear previous search results
    Worksheets("Results").Cells(1, 1).CurrentRegion.ClearContents
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Main" And ws.Name <> "Results" Then
            With ws.Cells
            Set c = .Find(Target, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows)
                If Not c Is Nothing Then
                    firstAddress = c.Address
                    Do
                        nr = nr + 1
                        c.EntireRow.Copy Worksheets("Results").Cells(nr, 1)
                        Set c = .FindNext(c)
                    Loop While Not c Is Nothing And c.Address <> firstAddress
                End If
            End With
        End If
    Next ws

End If

End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Lourry]

Thanks Loomah!!

Yes i am looking for the search data anywhere in the dataset not a specified column.

Is there a way to put that code in a button_click()?
I tried to do that but it seems to complain about the "ByVal Target as Range".

Thanks again for your help.

 

[Lourry]

OH, i changed it to a string and it worked! Also, I changed lookat:=xlWhole to lookat:=xlPart so it does not match whole value in cell.

THANK YOU SO SO MUCH LOOMAH!!

 

[Lourry]

Is there a way to only insert the row once to "Results" if the value happens to be in more than one column but the same row?

Thanks for your help!

 

[Loomah]

hi lourry
first things first - to attach the code to a button first create the button from the controls toolbox then right click to view code. copy the code i gave you but exclude the sub - end sub and also the if target.address line and the associated end if at the end of the code. not sure what you mean when you say you changed to string and it worked!

i used xlwhole as i use random numerical data to create things like this. if i used xlpart and entered 4 as the criteria i would get 14, 40-49, 84, 104 etc etc which isn't what i was looking for!

as for your final point i was thinking about that but didn't come up with a simple solution. this will need a little more thought on my part so hopefully later today. do you just want to take one row from each data sheet or only have one row left in results if there are identical rows in different data sheets?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

hi
this code removes the duplicate records from the report data set once all the extractions are done. if, however, you have data a, b, c from datasheet1 and a, b, c from datasheet2 these will be seen as the same thing. if you need to view them as different we will need to add an identifyer to the results data to show which data set it came from - haven't bothered for now!

if you're using xl07 turn on the macro recorder (i'm sure it still exists!) and use the 'remove duplicates' functionality and use that code instead. if you're not using 07 then just ignore that!

copy this to the end of the previous code (only add the declaration of lrow to the top with the other declarations)

Dim lRow As Long
With Worksheets("Report")
    lRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    .Cells(1, 1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .Cells(1, 1).CurrentRegion.Copy .Cells(lRow + 1, 1)
    .ShowAllData
    .Rows("1:" & lRow).EntireRow.Delete
End With

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Lourry]

Yes it worked! Thank you Loomah!

Is it easy to show on the result page which datasheet it came from? Perhaps just add the name of the datasheet to the first cell of each row in the "Results" page so the datasheets are not modified?

Again, thanks a lot for your Excel expertise!

-Lory

 

[Loomah]

hi
simple answer is yes!

below i've re written the code to incorporate changes through the whole thread. the thing to be aware of here though is that with the data sheet name added to results if you have 1,2,3 in data1 and 1,2,3 in data2 they will both remain in your results as adding the data source creates a unique record. this can be changed and as i type i have a feeling that's what you're going to ask for!!

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim c As Range, t As Range
Dim nr As Long 'new row
Dim firstAddress As String
Dim lRow As Long
Dim iCol As Integer
    
    Set t = Worksheets("main").Range("A1")
    nr = 0
    'clear previous search results
    Worksheets("Results").Cells(1, 1).CurrentRegion.ClearContents
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Main" And ws.Name <> "Results" Then
            With ws
            iCol = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
            Set c = .Cells.Find(t, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows)
                If Not c Is Nothing Then
                    firstAddress = c.Address
                    Do
                        nr = nr + 1
                        .Range(.Cells(c.Row, 1), .Cells(c.Row, iCol)).Copy Worksheets("Results").Cells(nr, 2)
                        Worksheets("Results").Cells(nr, 1) = ws.Name
                        Set c = .Cells.FindNext(c)
                    Loop While Not c Is Nothing And c.Address <> firstAddress
                End If
            End With
        End If
    Next ws

    With Worksheets("Results")
        lRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
        .Cells(1, 1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
        .Cells(1, 1).CurrentRegion.Copy .Cells(lRow + 1, 1)
        .ShowAllData
        .Rows("1:" & lRow).EntireRow.Delete
    End With
End Sub

if you want to reduce your data in results to one unique record regardless of source data change this line

.Cells(1, 1).CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, Unique:=True

with this

.Range(.Cells(1, 2), .Cells(lRow, .Columns.Count - 1)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

this will leave you with the first occurance of the data that was found so you will now see which data set it was from but will not know if it occured in other data set(s).

enjoy!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Lourry]

Thank you again Loomah!

Thanks for letting me know how to modify the code to make it unique if the decision was made to show less data.

However, when I run the program, I receive:

Run-time error '1004':
ShowAllData method of Worksheet class failed

Then it highlights the .ShowAllData line in the code.
Do you know why?

 

[Loomah]

yeah! it happens if the data was never filtered - ie all records in the results data are already unique.

there may be a neater way to work around this by checking whether there is a filter in place but this method should work as well.

add this line between filter code and the copy code

If .Columns("A").SpecialCells(xlVisible).Count = .Rows.Count Then Exit Sub

essentially it checks to see if the number of visible rows matches the total number of rows in the sheet. if there is a match then all records are already unique and there's nothing left to do.

happy friday!



;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

ok, so i was being lazy and 5 minutes more thought would have delivered this

If Not .FilterMode Then Exit Sub

much neater and tidier - just use this instead of the code in my last post, in the same place as i said before.

hoppy friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​