Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
--
Deirdre

Diddy,

Select your data table, then use Data / Pivot Table.... and click Finish.

Then drag the heading from column A to the Row area, column M to the Column Area, and Result to the
data area.

HTH,
Bernie
MS Excel MVP


"Diddy" wrote in message
...
Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
--
Deirdre

Hi,

A pivot table is a nice solution or if you want to use a formula

=SUMPRODUCT(--($M$2:$M$100=E$1),--($K$2:$K$100=$D2),$P$2:$P$100)

Where the test names are listed in E1:G1 for example, the persons identifier
is in D2 the formula above would go into E2 and be copied down and to the
right as far as necessary.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Diddy" wrote:

Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
--
Deirdre

Hi Shane,

The formula worked brilliantly. Would it work for text formats, I'm guessing
not - but haven't seen sumproduct used like this before.

Thank you so much :-)
--
Deirdre


"Shane Devenshire" wrote:

Hi,

A pivot table is a nice solution or if you want to use a formula

=SUMPRODUCT(--($M$2:$M$100=E$1),--($K$2:$K$100=$D2),$P$2:$P$100)

Where the test names are listed in E1:G1 for example, the persons identifier
is in D2 the formula above would go into E2 and be copied down and to the
right as far as necessary.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Diddy" wrote:

Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
--
Deirdre

Hi Bernie,

Very neat :-) I've never used pivot tables before and not as scary as I
thought

I will probably use Shane's formula for this one but will definitely be
using pivot tables again.

Cheers
Diddy
--
Deirdre


"Bernie Deitrick" wrote:

Diddy,

Select your data table, then use Data / Pivot Table.... and click Finish.

Then drag the heading from column A to the Row area, column M to the Column Area, and Result to the
data area.

HTH,
Bernie
MS Excel MVP


"Diddy" wrote in message
...
Hi,

I'm tying myself up in knots trying to get this to work.

I have employee test data in a single sheet.

K is a helper column with a unique number identifying the employee. M is the
Test and p is the result (1-5)

I would like to be able to set up a formula so that data stored in P can be
matched with employee in rows.

eg
Col A Col M Col P
Robert test 1 result 5
Robert test 2 result 4
Robert test 3 result 5

Emp test1 test 2 test 3
Change to Robert 5 4 5

Sorry I know not very clear
--
Deirdre