Employees & Managers

[NirOrtal]

Hi

I have Excel 2003.
I have list of employees and manager in sheet #1.
For example I have list of 20 employees in column A and list of managers in
column B.
I have only 5 managers, mean that I will see their names more than once in
column B.

In sheet #2 I have the same 5 managers, but this time as headers (columns
A-E).
How the names of the employees from sheet #1 can copy themselves under the
manager that they belong to in sheet #2?

 

[T. Valko]

Try this:

Using defined named ranges as follows:

Emp: refers to Sheet1A$2:A$21
Man: refers to Sheet1B$2:B$21

With your 5 manager names on Sheet2 in A1:E1, enter this array formula** in
Sheet2 A2:

=IF(ROWS(A$2:A2)<=COUNTIF(Man,A$1),INDEX(Emp,SMALL(IF(Man=A$1,ROW(Emp)),ROWS(A$2:A2))-MIN(ROW(Emp))+1),"")

Copy across to E2. Then select A2:E2 and copy down to row 21.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[NirOrtal]

THANX!!!!!!!

Try this:

Using defined named ranges as follows:

Emp: refers to Sheet1A$2:A$21
Man: refers to Sheet1B$2:B$21

With your 5 manager names on Sheet2 in A1:E1, enter this array formula** in
Sheet2 A2:

=IF(ROWS(A$2:A2)<=COUNTIF(Man,A$1),INDEX(Emp,SMALL(IF(Man=A$1,ROW(Emp)),ROWS(A$2:A2))-MIN(ROW(Emp))+1),"")

Copy across to E2. Then select A2:E2 and copy down to row 21.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

THANX!!!!!!!