Search through all worksheets in a workbook, present the results and avoid duplicates

[ThiaJay]

Hello there, I have a working xlsm file that is capable of searching and presenting the results of a prompt to search for any text entered from multiple worksheets into the first worksheet. It even produces clickable links to take the user to the original cell if they want to see it there.

It's working quite well but there is one issue. Two columns sometimes contain the same data, such as a location (for example, a business name in one column and in an address column but on the same row).

The code searches in order for any instance of the word input starting in column a, displays the results in each row and does so as it works its way through to column k. Column b is where the first instance is found (business name) and then column g (address). My first thought was to prevent the search from checking column g, but sometimes I need to find something based on the address.

It essentially finds relevant matches in these instances in column b, produces a list and then the results appear to be duplicates with it restarting again for subsequent matches. I need it to only display any given row once, can someone please help?

Please Login or Register  to view this content.

Reset module

Please Login or Register  to view this content.

The worksheet is highly sensitive so it would be very difficult to share it in any form, I imagine this code should be sufficient to determine what needs to be done.

Thank you so much for your help.

[jindon]

Try change "Set_Search" sub routine to

Please Login or Register  to view this content.

[ThiaJay]

Thank you so much, initial testing indicates that's just what I needed. I'll be sure to report back if there are any problems. I really appreciate your help (and how quickly you provided it too)!

[ThiaJay]

Try change "Set_Search" sub routine to

Please Login or Register  to view this content.

Hello Jindon, I've just noticed after testing that the initial results have the correct cell references but as soon as some of the duplicate results are hidden, the following cell references are wrong. Do you know how that can be fixed?

[jindon]

Find method will not search the cells that are hidden by the filter.

Why do you need to hide the Row/Column?

[jindon]

You need to loop...
Try change to

Please Login or Register  to view this content.

[ThiaJay]

This goes back to the original post,iIt essentially finds relevant matches in these instances in column b, produces a list and then the results appear to be duplicates with it restarting again for subsequent matches. I need it to only display any given row once.

The code works well but has caused the cell reference links to go out of sync in the search results, so when the cell reference link on the left of the worksheet is clicked on, it takes the user to the right worksheet but the wrong row.

[ThiaJay]

You need to loop...
Try change to

Please Login or Register  to view this content.

Thanks Jindon, it's coming up with an error though on "Sub Set_Search()".

Just to confirm, this is the code since I originally posted in case that makes a difference:

Please Login or Register  to view this content.

[jindon]

1)
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

2) Shut down my PC alrady, so I'll look at it tomorrow.

[jindon]

I can not think of anything other than you have Error value in the range, so change the line in bold

Please Login or Register  to view this content.

If this doesn't work, I need to see your workbook.

[ThiaJay]

Thanks Jindon,

Can you please confirm what the code should look like in its entirety with the updated code from my last post in conjunction with yours?

[jindon]

Just forget about YOUR code.

I'm talking based on the code I gave you in post #6.

Please Login or Register  to view this content.

[ThiaJay]

I can't ignore it entirely because there are other worksheets that need to be excluded from the search and the cells to search have changed. So, is this how it should appear, with this in mind?

Please Login or Register  to view this content.

If this is right, it's not behaving as it should and either searches produce no results when I know that they should (and they appear with the old code, albeit with the wrong link/cell references) or it displays links/cell references but no data to the right.

I've probably missed something when updating the code you shared with the relevant cell references and exceptions.

[jindon]

No more suggestion without seeing your workbook.

[ThiaJay]

Understood.

[jindon]

Then what's the problem?

Both codes that I have posted are working fine to me.

[ThiaJay]

Have you tried modifying the code in ThisWorkbook to match your suggestion, but with the exceptions and new cell references instead?

[jindon]

I have tested the code that was attached to the file "Sub Set_Search".
Also tested the one I last posted changing If statement to exclude the sheets and working fine.

You should have known that if the cell is linked to hidden cell, the selection is hidden.

[ThiaJay]

Could you please share the modified code you used to test it in the dummy workbook? I don't want hidden cells to be shown in the search results at all. I do want the search results to have the corresponding cell link when the cells are not hidden.

[jindon]

Set_Search is the one attached, Set_Search2 is the one using loop.

Because you set Worksheet_Activate event code to select A1,
Following code is added to Search sheet

Please Login or Register  to view this content.

[ThiaJay]

I've tested your workbook and this is what comes up:

7zBmHIg.png

Links but no data to the right.

Please Login or Register  to view this content.

I've attached a copy with dummy data from a website to demonstrate, I understand that none of it is real even though it looks like it is (it's not my data).

[jindon]

Please Login or Register  to view this content.

[ThiaJay]

Thanks Jindon, can you please explain exactly how it is working now? I am getting mixed results. Now, when I run the test on my live workbook, it brings up the first results before the problem with the links occurs and omits everything else. When I try searching for a lot of other data that exists in the workbook, it says there are no results and I know there should be?

[jindon]

Is it working on your posted workbook or not?
My concern is only the workbook I'm working with.

I am getting mixed results. Now, when I run the test on my live workbook, it brings up the first results before the problem with the links occurs and omits everything else. When I try searching for a lot of other data that exists in the workbook, it says there are no results and I know there should be?

What's "my live workbook"?
No comment/suggestion for the workbook that I don't have.

[ThiaJay]

My live workbook is an identical copy of the workbook you are working on with real data in it that I cannot share. It behaves as described above.

Try searching for a partial match such as netlog (part of an email address) and it comes up with no results, when that data exists in the dummy workbook.

[jindon]

If your EXCEL is behaving like that I can not help you anymore.
It is working just fine here.

Good luck.

[ThiaJay]

Please check above.

[ThiaJay]

Try searching for a number, like 4. There should be more results coming up if it's checking every cell.

[jindon]

......................

[jindon]

One thing I can think of is you have dust in col.A of "Search" sheet.

Select first empty cell in col.A, hit Ctrl + down arrow.
If you get cell A1048576, you Excel is something wrong.
Otherwise you need to clear all those cells.

[ThiaJay]

In local contacts, there are 1162 instances of 4.

tYxJcDI.png

Why is it only coming up with three results in all three of the worksheets?

[jindon]

OK that was originally xlPart...
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[jindon]

And Reset1 needs to be like this, row number is not enough.

Please Login or Register  to view this content.

otherwise it will paste to far below like you currently experiencing.

[ThiaJay]

Thanks for your reply jindon. I am currently testing, initial results are promising.

[jindon]

If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

[ThiaJay]

Hello Jindon, I will do after more thorough testing. Thanks.