Thomas

If this is a one-time requirement, look further down in this post for
simple approaches. If it is an on-going requirement that needs to be
done automatically do the following:

Put the name of the department in cell A1 of its own sheet. In cell A2
of that sheet put:

=IF(COUNTIF(sheet1!$B$2:$B$15,$A$1)=ROW($A1:$A1), INDEX(sheet1!$A$2:$A$15,SMALL(IF(sheet1!$B$2:$B$15 =$A$1,ROW(INDIRECT("1:"&ROWS(sheet1!B2:B15))),""), ROW($A1:$A1))),"")

Enter this formula as an array formula, that is, instead of pressing
Enter, press Ctl-Shift-Enter at the same time. This will automatically
put curly brackets around the formula indicating that it is an array
formula.

Then copy/drag this formula down as far as you need to to cover all the
employees in that department.

Do this for each department on different sheets.

Alternatively, if you only want to do this one time, use AutoFilter on
the column B to select each department and then copy column A and paste
special values into the appropriate sheet.

Or, if all else fails and this is a one-time effort, use the formula
you have been using, then sort column A and delete the blank cells, and
copy/paste special the employeeIDs as values.

Hope this helps

Declan O'R

thomasNg wrote:
dear forum,

help please.
i have 2 columns in sheet 1: col A is employee ID, col B is Dept name.
i want to search col B, match it with the employee ID in col A.
i have several other sheets so that each sheet shows only the employee
IDs for one particular dept.

so far i have used a simple if(dept="marketing",a1,"--"). i am not too
sure how to use vlookup in this case. also i dont want to skip rows.

this is what i am getting with my formula
sheet 1 sheet 2
sheet 3
1012 Acct --
1012 acct
1064 Marketing 1064 marketing
--
2066 Admin --
--
5066 Acct --
5066 acct
6654 Marketing 6654 marketing
--

sorry this post is longwinded, but i thought i'd give as much b/ground
info as possible.

thanx

thomas.


--
thomasNg
------------------------------------------------------------------------
thomasNg's Profile: http://www.excelforum.com/member.php...fo&userid=8375
View this thread: http://www.excelforum.com/showthread...hreadid=487834