Scheduling...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
Click to expand...

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.

---
 
Thanks a lot. Works like a charm.

Max said:
One way ..


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.
Click to expand...
 
Back
Top