Employees & Managers
 

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?

Employees & Managers
 

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)

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
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?

Employees & Managers
 

THANX!!!!!!!

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
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?

Employees & Managers
 

You're welcome!

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
THANX!!!!!!!

"T. Valko" wrote:

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)

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
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?