Assistance in creating a "Search" function for large data sheet

[Ianseconds]

Good Evening,

I've had great success using this site, I want to thank everyone prior to their help beforehand.


Objective
To create a search function on Sheet1 ("Search") that will find the particular data from Sheet 2 ("Global Data") which will then populate back to Sheet1.

Explanation
I have an employee data spreadsheet that is sent to me three times a week. I'm always using this sheet to find employee information, and I'm tired of using the Find & Replace, then having to scroll over and...yeah. "First World Problems".

What I envision, is on Sheet1, I can enter an Employee Name and have their information populate on the sheet under the correct categories. (see attached) This way, I can print off the sheet that is formatted and present the information without having to do any other legwork.

I understand this will require some sort of VBA code to be written, which I'm asking if that could be done. I also know I might need some sort of Command Button at the top after the search field, so I'm not locked down on the basic formatting I've provided on Sheet1.

Possible Issue

1. Say the employees name is Ian Keller. I'd like to be able to search for the last name, Keller, and have the information populate. Though, there will more than likely be other employees on my database with the last name of Keller. Is there a way, after searching, I will be able to select which one I'm looking for? I've searched for this on other sites and found some helpful information, but not exactly what I was looking for. I do realize, I might not even need the "Active Search Box" and could use the cell. Either way, or anyway, to do this I'm up for.

2. There will be more fields than what is provided, so I'm sure I can manipulate the code to what I need once built.


In the end, I basically want a glorified "Ctrl F" and have it populated onto my Sheet1. Is this possible? Can anyone assist?

I will be more than happy to give any additional information if, and what will probably be needed. I'm pretty decent in Excel, but getting around VBA and Macros is a bit of a pain.



Thank you again in advance!

[scott.s.fower]

totally possible, i created a similar thing for someone a while back, but it is a lot of work to make sure it is totally debugged
would take the better part of a day, i'll see what i can do though, maybe some one has a similar project already done?

[Ianseconds]

Scott, thanks for the reply!

Good to know it CAN be done. Would it help if I created a dummy spreadsheet that had the exact number of columns I'd need? I can create that now if it helps.

Another thought I had...The data sheet is for all current active employees. Since I receive it 3 times a week...Employees come and go due to Terminations, Hires, Transfers, etc which means there is always a different amount of rows (columns constant). So, if the sheet currently had 4500 rows, but 100 people were hired making it 4600, will the code be able to catch these without having to be altered?

if that makes sense...

[Ianseconds]

I tried something like this before when searching for easier ways to build this.

http://alm-excel.blogspot.com/2011/1...play-data.html

maybe something like this could/would be easier? I couldn't get it to work, though, when building it.

[Ianseconds]

Here is an updated file. The columns will be the constants, while i only have 200 rows displayed, there is currently over 4000

[scott.s.fower]

rows are not a problem (i think)

[FDibbins]

Here is a possible solution for you. However, it depends on your ability to sort the data on DATA sheet.

Then, all you do is enter a last name where I have highlighted in red, a list of people with that last name will appear in the yellow area, you select which 1 you want by putting an x next to their name...and your table gets populated. You will need to adjust the ranges, I took it down to 600

[AB33]

See attached code.
Please note the following:
VBA does not like merged cells, some people even swear they will never touch a sheet which has merged cells, so my code will not work unless I unmerged the cells. I removed all the merged cell in column B of search data sheet.
You have two options of feeding the search item in the data sheet. First, to put the search the items on cells, which is what I am doing, i.e. Range H2, so you put the value to be searched in H2 and run the code, you get the result. The code first clears all the existing data, except the headers from search sheet.
The second option is to put the search item in input box, so that the code asks you what you want to search and you then type the items. This is probably not what you wanted.
When you search for an item, if there are duplicates with the same name, the code returns the first item. As you can from the attached example, you have three briggs. The code returns the first cell it finds. A more refined option would be to use Surnames as you see on my second e.g.
The whole process could be done auto by triggering an event, but would not advice you at this stage, just pressing the button (Column O) does the job.

[Kyle123]

Auto-Filter is your fastest and most simple method, have a look at the attached, where multiple results are returned, double-click the name you want and the summary detail is populated.

[Ianseconds]

I'd like to thank all of you for this amazing response. This is SUCH great help.

So I followed through with all of the above spreadsheets.

FDibbins: Your solution provides me an easy fix. Though the sorting of the data does make it a bit more difficult. Thank you!

AB33: While you're suggestion, the first one, allows me to use a command button which I do like, I'm a little worried on if something changes with my file how easy it will be for me to go in and make changes to it. I'd like to mess with it a little and see what I come up with. Thank you!!

Kyle123: Your spreadsheet does seem the easiest, which will be helpful in me explaining for others how to use it. It allows me to take the data sheet I'm sent by email, and just copy and paste directly into this file, without doing any altering. It also lets me keep the format of my output sheet, which I like.

A few questions, for you Kyle:

Of course today would the the day that we receive a new data sheet with more columns added. If I make the changes to my SEARCH sheet and add new fields, how can I make sure those new columns from the DATA sheet will be reflected? Is this easy? Is there an easy way for you to show me how you created the macro? Or should I just upload a new dummy sheet with my new columns/new blank fields in my search output box.?


Again, thank you all for your help!

[Kyle123]

It depends on the extra columns, if the first 3 are still the same then the macro should work. In terms of getting the detail info, it just uses VLookup, so you should just be able to adjust the formulas to bring back what you want

[Ianseconds]

great! I should have noticed it did a vlookup. This helps a ton. Thank you so much.

[FDibbins]

Happy to help and thanks for the feedback

[Ianseconds]

Kyle...One last thing.

Is there anyway I can have EmplID as the First of my 3 columns? Name as the Second, and Corporate title as the third, without changing too much?

[Kyle123]

It's a bit of a Pain in the **** to be honest, it adds in quite a bit of complexity since your data isn't in that order to begin with - I can have a fiddle but it will make things more awkward than they need to be. Let me know if you really need that Thanks for the feedback

[Ianseconds]

No problem. My data does actually come in that way, I must uhave just messed up the column when creating my dummy sheet. Its really no big, I just alter it before i put into this workbook.

A question was asked to me if there is a way to do a dual search. Say, I search for the name Smith and 15 come up. Then I search USA in the 2nd box and it limits my Smiths to 5.

I mean I'm sure there's a way, but more of a pain. haha.

[Ianseconds]

So, My team has requested that the Employee ID be back as the first column.

If I were to upload a new file with the data set this way, would you be able to assist? I hope its not too much of a pain.

[Kyle123]

Sure, it's trivial if the raw data is in that format

[Ianseconds]

thanks, Kyle! I really appreciate the help!

attached is the .xlsm file with the empl id in column A. I added more columns to this than what was on the previous file, and though the data doesnt make sense for some of them, its no big. my v lookups are working.

Thanks!!!

[Ianseconds]

Hey Kyle. If you have chance could you fix the macro?

[Ianseconds]

Hello Again,

So I'd like to thank Kyle again for helping me with this search function earlier this year. Still have one issue though...

See the attached file. My problem is that on my data sheet, the way my data comes initally is Employee ID is in Column A, not in Column B. So anytime my team or myself opens this file just to cut and paste the data to another sheet, we constantly have to swap Column A & B with eachother. Small and annoying yes, but would be great if I could have it fixed.


Every time I mess with the macro it craps out on me. Any assistance would be greatly appreciated.


Basically:

On Data Sheet, Swap column A & B so that Employee ID is in Column A
On Search Sheet, still be able to search by Name and have results displayed.


This is using AutoFilter & a Macro