Creating Find Tool for Directory

[isaacjohny]

Hi,

I am new to this forum and realized that Excel- New users is for beginers only. I'm posting my question in programming section.

I am preparing Vendors' Directory for our company. This Directory has different products listed in seperate sheets. The names, phone numbers and address of various vendors' are listed in each sheet.

My problem is that I want to create a new sheet with a find tool that will search particular name of the product or the vendor from all the sheets (say product A, product B) and list out complete information in single page.

I have attached a sample excel sheet which is supposed to search the name "Daniel" from sheets - Product A and Product B and list it out in the first sheet. The sample does not work.

I would be glad if someone could find a solution for this.

Johny

[royUK]

It's best when setting up tables that you do not include any blank rows such as you have between the header row & the data.

I don't see why you don't have all the data on one sheet,then all you would need would be either AutoFilter or AdvancedFilter.

[mudraker]

isaacjohny

Welcom to Exceltip forum

Please take a couple of minutes and read the forum rules

An Announcement thread that covers the rules is at the top of each forum or you may use the link below in my signature.

Your duplicate thread in Excel - New Users has been deleted

[royUK]

If you are looking for a VBA solution then your problem will be that the string you are searching for is only part of the name.

[TestPoster]

Try the attached!

[royUK]

This code will find Daniel within the cells contents

Please Login or Register  to view this content.

[isaacjohny]

Hi Roy,

Thanks for the reply. Firstly I want to apologise for the cross posting. Is it possible to delete a post if we realize that it has been posted in wrong forum?

In this first visit to this website I found some few questions which were quite close to mine. I was wondering if there are any VBA solutions which could search the name (either supplier or product) string and if available copy and paste the entire row (containing details) with all similar names in a single page.

Since I'm not that good in excel i would be glad if there is readily available solution. If its not that simple, would appreciate your guidance.

Thanks,
Johny

[royUK]

Try the example I have uploaded, it searches for the name.

[isaacjohny]

Thanks a lot! Thats what I wanted

Seems like a magic to layman like me.

Thanks again!
Johny

[isaacjohny]

Hi Roy,

Thanks for the VB program for find tool which was of great help. However there are few problems that i encountered while running it.

Firstly, its caps sensitive, i.e. if I search daniel instead of Daniel it doesnt work

Secondly it doesn't delete the items of old search, i.e if we search 'Daniel' first and 'Holford' next, the items of Daniel search still remains. Is there a way to reset that area or delete the old items before pasting newly searched items?

Thirdly this program copies the colour filled in that particular cells as well when it pastes in front sheet. Is there a way to paste only text in the cells and not the colour filled in that cell?

I tried a lot but in vain. Would you have a easy solution for this as well.

Thanks for everything.

Johny

[mudraker]

This will fix the case sensitive & clear old search items part of the problems
I do not have time at the moment to help with the pasting format problem

Replace existing code with this code

Please Login or Register  to view this content.

[manny_cb]

hey guys.. im trying to do something similar.. is it possible to make the search results include partial matches.. so say if i search for "jo" it would return john and joe ?? thanks for the help

[mudraker]

manny_cb

Please take a couple of minutes and read the Forum Rules & start your own thread - Rule 2

Closing this thread