Selecting an unmatched value

[Matthew]

Help,
Its a ROSTA thing

Sheet 1
I have a list of 500 sites in col A
In cols B through N I have entries for people who could cover the site
in A.

Some may be 1 person some maybe 12 people.
Sheet2
What I am trying to do by any method is on a separte sheet to
automaticaly select from the list on sheet 1 a person to cover the
centres listed.

The bit I am stuck on is that the people listed on sheet may be able
to cover a number of sites. So the choice I would like to make would
only use a person not used already.

I think I would need to send an example so if anyone wants to help let
me know because at the moment all i have is this
=IF(ISERROR(MATCH(VLOOKUP($D100,who,3,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,6,FALSE),VLOOKUP($D100,who,
7,FALSE)))))))

Where:
D is the site number column
P is the column that I am comparing (obviously gets biger as we go
down)
Who is the range that I am checking against

As you can all see a complete mess, not my best moment.

Please help

Matthew

 

[Don Guillett Excel MVP]

Help,
Its a ROSTA thing

Sheet 1
I have a list of 500 sites in col A
In cols B through N I have entries for people who could cover the site
in A.

Some may be 1 person some maybe 12 people.
Sheet2
What I am trying to do by any method is on a separte sheet to
automaticaly select from the list on sheet 1 a person to cover the
centres listed.

The bit I am stuck on is that the people listed on sheet may be able
to cover a number of sites. So the choice I would like to make would
only use a person not used already.

I think I would need to send an example so if anyone wants to help let
me know because at the moment all i have is this
=IF(ISERROR(MATCH(VLOOKUP($D100,who,3,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$599,0))=TRUE,VLOOKUP($D100,who,6,FALSE),VLOOKUP($D100,who,
7,FALSE)))))))

Where:
D is the site number column
P is the column that I am comparing (obviously gets biger as we go
down)
Who is the range that I am checking against

As you can all see a complete mess, not my best moment.

Please help

Matthew

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."