Hi,

To make it work, you have to array enter the formula (Ctrl+Shift+Enter)

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
Thank you, this is a big help. I'm having trouble copying this as an
array
(I'm using the ISERROR version). As soon as I make it an array formula,
it
returns a blank cell as if there is an error. And if I don't use an array
formula, and copy it down, it changes the row reference. Do you know why
this wouldn't work as an array?

Thanks again, appreciate it!


"Ashish Mathur" wrote:

Hi,

You may want to take a look at my solution on the MS website

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CW" wrote in message
...
Hi, I get an HR report that lists in one column the dept # and in the
next
the employee name... so there are multiple rows with the same dept#. I
want
to manipulate the data so I can list all employees in a department in
one
column (for example, column A would be Dept 100 and in rows 2-10, it
would
list all 9 employees, and then in column B, Dept 200 would be, etc).
This
will allow me to set up an HLOOKUP table.

Is there some sort of automated way to manipulate the HR report or do I
have
to copy and paste one by one?

Would appreciate any help.

Thanks.