Macro for Searching the data from sheet 2 and input in sheet1

[ashish navale]

Hi,


I have a file in which there are details of employees like first name, last name, emp id, dob etc. These details are available in sheet2. There are nearly 50-60,000 employees. My requirement is that I need a macro which will search data from sheet2 and give me output in sheet1. Eg.If I enter Michael in B2 cell of sheet1 and run the macro all names having Michael should be displayed in sheet1 under particular format as below:

A B C D E

Emp Id First Name Last Name Middle Name Birth Date


this format is on row no.4 starting from A column

Note that the same format is in sheet2. When I run Macro to search any particular name or any other detail like dob it should display all details related to it in sheet1 in same format as shown above.

Please help me.

 

[SirJB7]

Hi, ashish navale!


Have you considered using filters in sheet2 row 4? then copy resulting data to sheet1.


Regards!

 

[prasaddn]

Hi Ashish,


I would suggest you to have sheet 1, B2 cell instead of name, use employee ID. Unless name is unique in sheet2.


By this you can have vlookup formula in sheet1 with source data from sheet2.


I have used such method and it works very beautifully..


Regards,

Prasad DN

 

[ashish navale]

Actually I don't want data which is unique. When I say I want to find any name, I want a result which will throw data having all the similar names, as some times the emp ids are absent and only data I have is name.


Is there any solution for this??

 

[Luke M]

As SirJB7 posted earlier, I think your best bet is an AutoFilter. If you want to make it a little faster/fancier, I'd suggest using Roger Govier's FastFilter, at the end of this article:

http://blog.contextures.com/archives/2012/02/07/excel-autofilter-by-typing-criteria/

 

[NSMURTHY]

Sir

I am having excel sheet containing 2 sheets wherein employee data is there. Sheet1 is Master data. In Sheet2 employee data repeated several times.
Based on the master data I have to count how many times data repeated and highlight the same or copy to other sheet.
I have tried countif and succeeded. I don't know how to highlight or seggregated. excel workbook by name TestV.xlsx enclosed.
Early reply solicited

 

[vletm]

NSMURTHY
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

You seems to skipped few basic rules.
eg from How to get the Best Results at Chandoo.org - 9th part.

Note: This thread has been active ... few years ago.