search by employee id, display rows on another sheet.

[sark666]

On sheet1 I have this:

employee ID Date hours item1 item2 item3 item4

So it's 8 columns and the row count can vary.

This list on sheet 1 has the persons employee id and various bits of info. There will be multiple rows with the same employee id. This data is not sorted in any particular order, but I'd rather leave this sheet untouched.

On another sheet, I would like the user to simply type in a number for the employee id (I don't want to use msg box, just a cell is fine, maybe i'd just add a button form to click search to run the macro) and it lists all their hits, however many rows that may be. Again I can't sort the original list so the employee might have hits scattered throughout the main list.

So they would enter the employee id and below that they would get all the rows with all the above data where they get a hit. One concern I have is say the first search for employee 123456 turns up 18 results. Then they search for employee 123444 and it only returns 8 results. I don't want the last 10 rows of employee 123456 to be still present. Basically a search that searches another sheet and returns any hits, and it clears the list of previous search results so for each search you only see the results of that search and not old data.

[JBeaucaire]

It is against forum rules to CROSSPOST without including links to the other forum(s). This is to insure no one is spending time on an issue resolved elsewhere already, you'd hate to waste volunteer's time, wouldn't you?

http://www.mrexcel.com/forum/showthread.php?t=410354

Be sure to read the Forum Rules, they will greatly enhance your effective use of this forum.

[royUK]

Why re-invent the wheel, use a Pivottable.

Take a note of what you have been told about cross posting & the Forum Rules

[sark666]

I did not know of that rule, I thought these forums were completely separate entities. I am sorry about that.

I'll test the persons solution and mark both as solved. Thanks for all your help.

[royUK]

The Forums are different, but that doesn't mean that you should post in each one without a warning. Why should members at either Forum waste time in a post that may already be solved?

Anyway, check out using a Pivottable

http://excel-it.com/clear_code.htm

[sark666]

Again, my apologies for the cross-post. But ya, there are reasons (too hard to explain) that a pivot table would not be appropriate nor would using auto-filter. I need a vba solution where the user just types in a employee id and gets any rows pertaining to that individual. So the search would be on a separate sheet, reference the other sheet for hits, and display them on the search sheet.

I've tried the person's reply in the other forum but I haven't had luck in getting it to work. I've posted a follow-up in that forum. If I get a solution, I'll post it to both and mark as solved in case it helps someone else out in the future.

thanks again.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!