Good day,

I'll start by explaining what I am trying to accomplish but I might not be
using the proper terms.

I want to have a list or database of, let's say, 10 "assessors". The list
would have "assessor1" to "assessor10"

For the purpose of this e-mail, let's say that there are 6 tests/assessments
that need to be made. So "test1" to "test6"

Each student will have an assessor assigned for each test. Assessors need
to be different for each test. In other words, an assessor cannot assess the
same student twice.

So my need is to have a formula that would return the list of assessors that
have not been assigned to a student.

So if each row correspond to a student (column A), and each column to an
assessor(column B:G), I would like to have a formula at the end of the row
(so in column H) that would return the assessors not found in column B:H for
that particular row.

Any help would be much appreciated.

Maxime

One way ..

.. my need is to have a formula that would return the list of assessors that
have not been assigned to a student.

Assuming assessor data in cols B to G, from row2 down
(it's presumed all cells per row are filled with different assessors, ie
there's 6 different assessors from amongst the pool of 10 per row within cols
B to G)

Define a 10 cell column range containing "assessor1" to "assessor10" as:
Assessors

Select an adjacent 4 cell horiz range, eg select H2:K2, place this into the
formula bar, then press CTRL+SHIFT+ENTER (instead of just pressing ENTER) to
confirm the formula:

=INDEX(Assessors,LARGE(ISERROR(MATCH(Assessors,B2: G2,0))*ROW(A$1:A$10),COLUMN(A:J)))

H2:K2 will return the 4 remaining assessors from amongst the pool of 10 for
row2. Select H2:K2, copy down as far as required to return correspondingly
for other rows.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Maxime Maugeais" wrote:
Good day,

I'll start by explaining what I am trying to accomplish but I might not be
using the proper terms.

I want to have a list or database of, let's say, 10 "assessors". The list
would have "assessor1" to "assessor10"

For the purpose of this e-mail, let's say that there are 6 tests/assessments
that need to be made. So "test1" to "test6"

Each student will have an assessor assigned for each test. Assessors need
to be different for each test. In other words, an assessor cannot assess the
same student twice.

So my need is to have a formula that would return the list of assessors that
have not been assigned to a student.

So if each row correspond to a student (column A), and each column to an
assessor(column B:G), I would like to have a formula at the end of the row
(so in column H) that would return the assessors not found in column B:H for
that particular row.

Any help would be much appreciated.

Maxime

Thanks a lot. Works like a charm.

"Max" wrote:

One way ..

.. my need is to have a formula that would return the list of assessors that
have not been assigned to a student.

Assuming assessor data in cols B to G, from row2 down
(it's presumed all cells per row are filled with different assessors, ie
there's 6 different assessors from amongst the pool of 10 per row within cols
B to G)

Define a 10 cell column range containing "assessor1" to "assessor10" as:
Assessors

Select an adjacent 4 cell horiz range, eg select H2:K2, place this into the
formula bar, then press CTRL+SHIFT+ENTER (instead of just pressing ENTER) to
confirm the formula:

=INDEX(Assessors,LARGE(ISERROR(MATCH(Assessors,B2: G2,0))*ROW(A$1:A$10),COLUMN(A:J)))

H2:K2 will return the 4 remaining assessors from amongst the pool of 10 for
row2. Select H2:K2, copy down as far as required to return correspondingly
for other rows.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Maxime Maugeais" wrote:
Good day,

I'll start by explaining what I am trying to accomplish but I might not be
using the proper terms.

I want to have a list or database of, let's say, 10 "assessors". The list
would have "assessor1" to "assessor10"

For the purpose of this e-mail, let's say that there are 6 tests/assessments
that need to be made. So "test1" to "test6"

Each student will have an assessor assigned for each test. Assessors need
to be different for each test. In other words, an assessor cannot assess the
same student twice.

So my need is to have a formula that would return the list of assessors that
have not been assigned to a student.

So if each row correspond to a student (column A), and each column to an
assessor(column B:G), I would like to have a formula at the end of the row
(so in column H) that would return the assessors not found in column B:H for
that particular row.

Any help would be much appreciated.

Maxime

welcome, good to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Maxime Maugeais" wrote in
message ...
Thanks a lot. Works like a charm.