Help!!! Need formula for boss

[DebbieV]

I have a worksheet with a list of employees and each employee has a
security profile allocated and each profile has a position number.
Now an employee can hold multiple profiles and then of course,
multiple position numbers. (see example below)

A B C
1 Emp Profile Posn
2 Jones PROF1 ABC123
3 Jones PROF4 DEF345
4 Smith PROF1 GHI678
5 Fraser PROF2 JKL912
6 Smith PROF4 MNO345
7 Fraser PROF3 PQR678

Now in the same file I want to have a worksheet where the details will
be shown as below:

A B C D E
1 Emp PROF1 PROF2 PROF3 PROF4
2 Fraser JKL912 PQR678
3 Jones ABC123 DEF345
4 Smith GHI678 MNO345

Can anyone help?

 

[Ron Rosenfeld]

I have a worksheet with a list of employees and each employee has a
security profile allocated and each profile has a position number.
Now an employee can hold multiple profiles and then of course,
multiple position numbers. (see example below)

A B C
1 Emp Profile Posn
2 Jones PROF1 ABC123
3 Jones PROF4 DEF345
4 Smith PROF1 GHI678
5 Fraser PROF2 JKL912
6 Smith PROF4 MNO345
7 Fraser PROF3 PQR678

Now in the same file I want to have a worksheet where the details will
be shown as below:

A B C D E
1 Emp PROF1 PROF2 PROF3 PROF4
2 Fraser JKL912 PQR678
3 Jones ABC123 DEF345
4 Smith GHI678 MNO345

Can anyone help?

In your data table, NAME your columns using the label at the top of the column (e.g. Emp Profile Posn

On another worksheet, enter your list of employee names in column A starting in A2; and your list of profiles in Row 1 starting at B1.

Then:

This formula must be **array-entered**:

For Excel 2007 or later:

B2: =IFERROR(INDEX(Posn,MATCH(B$1,IF(Emp=$A2,Profile),0)),"")

For older versions of Excel:

B2:
=IF(ISNA(MATCH(B$1,IF(Emp=$A2,Profile),0)),"",
INDEX(Posn,MATCH(B$1,IF(Emp=$A2,Profile),0)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

With your data, you should see a result like:

Emp PROF1 PROF2 PROF3 PROF4
Fraser JKL9120 PQR678
Jones ABC123 DEF345
Smith GHI678 MNO345

 

[DebbieV]

In your data table, NAME your columns using the label at the top  of the column (e.g. Emp   Profile   Posn

On another worksheet, enter your list of employee names in column A starting in A2; and your list of profiles in Row 1 starting at B1.

Then:

This formula must be **array-entered**:

For Excel 2007 or later:

B2:  =IFERROR(INDEX(Posn,MATCH(B$1,IF(Emp=$A2,Profile),0)),"")

For older versions of Excel:

B2:
=IF(ISNA(MATCH(B$1,IF(Emp=$A2,Profile),0)),"",
INDEX(Posn,MATCH(B$1,IF(Emp=$A2,Profile),0)))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>.  If you did this
correctly, Excel will place braces {...} around the formula.

With your data, you should see a result like:

Emp     PROF1   PROF2   PROF3   PROF4
Fraser          JKL9120 PQR678
Jones   ABC123                  DEF345
Smith   GHI678                  MNO345

It worked! Thanks Ron. BUT - they were so happy that they have given
me another file but this one is complicated. The position numbers can
be applied to more than one emp. What would be the formula to return
a YES or NO (or TRUE or FALSE) instead of a position number. As there
may be emps with the same profile but with multiple position number it
would only need to recognise the once if they have the profile.

Emp     PROF1   PROF2   PROF3   PROF4
Fraser  NO YES YES NO
Jones   YES       NO          NO YES
Smith   YES NO NO YES


Hope you can work your magic on this one.

 

[Ron Rosenfeld]

It worked! Thanks Ron.

Glad to help. Thanks for the feedback.

BUT - they were so happy that they have given
me another file but this one is complicated. The position numbers can
be applied to more than one emp. What would be the formula to return
a YES or NO (or TRUE or FALSE) instead of a position number. As there
may be emps with the same profile but with multiple position number it
would only need to recognise the once if they have the profile.

Emp     PROF1   PROF2   PROF3   PROF4
Fraser  NO YES YES NO
Jones   YES       NO          NO YES
Smith   YES NO NO YES


Hope you can work your magic on this one.

This one is simpler:

B2: =IF(SUMPRODUCT((Emp=$A2)*(Profile=B$1))>0,"Yes","No")

Fill down and across as before