Help on filling in cells based on the value of another cell(s)

Hi all,

I'm stuck and hoping you guys can help me out...once again.

I have a spreadsheet where Column A contains a person's employee ID, Column B may (or may not) contain what department they are in. For example:

Emp123
Emp123
Emp123 Finance
Emp123
Emp123
Emp456
Emp456
Emp456 IT
Emp456
Emp789 Operations
Emp789

What I am trying to do is populate Column B with the person's department if it is not already populated, based on a value where it is populated. Each EmpID will only have one value in Column B. Desired Result for above would be:

Emp123 Finance
Emp123 Finance
Emp123 Finance
Emp123 Finance
Emp123 Finance
Emp456 IT
Emp456 IT
Emp456 IT
Emp456 IT
Emp789 Operations
Emp789 Operations


Any help would be greatly appreciated!!!

In a new column enter formula:

=LOOKUP(2,1/(($A$2:$A$12=A2)*($B$2:$B$12<>"")),$B$2:$B$12)

adjust ranges and copied down

where A2:B12 contains current list.

Then you can copy this new column and paste special >> Values over original column B.