Selecting an unmatched value

M

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
$5:p99,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,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
 
D

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
$5:p99,0))=TRUE,VLOOKUP($D100,who,
3,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
4,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,0))=TRUE,VLOOKUP($D100,who,
5,FALSE),IF(ISERROR(MATCH(VLOOKUP($D100,who,4,FALSE),P
$5:p99,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."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Text to End of Footnote 9
Macro 3
V Lookup multiple entires 1
Sum Vlookup where there are blanks 2
#VALUE! 10
formula question 2
VLookup or Macro? 0
Searching the formula (not the value) for a text string 1

Top