Check if values are in a list

**rhysa** · 02-21-2012, 10:17 AM

Hi,

Just needing some help with checking some logic on a web based scheduling system. Need to verify the results in Excel to see the system is taking the constraints into account correctly. Scenario is

Candidates for an exam can request to sit the exam with certain people to save on travel costs e.t.c and also to specify dates they are not available to sit the exam on. So a file is loaded into the system like attached.

We need to verify the outcome using Excel to check that the candidates are not scheduled to sit an exam on a date they have said they are not available but also that they are scheduled to sit the exam with any travel partners they have specified. I know how to check the first bit for the dates, having issues with the second bit of checking that the candidates are scheduled together. Any ideas how I might check for this?

Thanks

scheduling.png

**dilipandey** · 02-21-2012, 10:22 AM

Hi rhysa,

Welcome to the forum.

Please post an sample workbook as an image will not help in practicals. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rhysa** · 02-21-2012, 10:29 AM

Sorry, attached one here now

SampleScheduling.xls

**rhysa** · 02-21-2012, 10:30 AM

Sorry, wrong one, hang on

**rhysa** · 02-21-2012, 10:32 AM

This is correct one, sorry!SampleScheduling.xls

**dilipandey** · 02-21-2012, 10:54 AM

Hi rhysa,

For 10th of Feb, below can sit with 6060227:-

6132932 6134903 6103214 6100233 6114796

but you have allowed only 6060227, 6114796, 6132932... Not able to understand, please provide more details. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rhysa** · 02-21-2012, 10:59 AM

No, shouldn't be looked at as for each date and who is available, instead, it should be looked at like this

Candidate 6060227 cannot sit the exam on 10/02/2012, 15/02/2012, 16/02/2012, 17/02/2012, 13/02/2012 or the 11/02/2012 but is available on any other date. However, candidate 6060227 must be scheduled on the same date that 6132932, 6134903, 6103214, 6100233 and 6114796 are scheduled.

Apologies for confusion

**dilipandey** · 02-21-2012, 11:05 AM

Hi rhysa,

But 6060227 is shown under 2012-02-10 in the results .... I am confused.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rhysa** · 02-21-2012, 11:08 AM

Yes, so in that case the check would fail. I'm needing to check if the system is scheduling them correctly based on their constraints or not. So, in the case of 6060227 the system is incorrectly scheduling it on the 10/02/2012. In my example I tried to include a mix of ones that should 'pass' and some that should 'fail'

**dilipandey** · 02-21-2012, 11:23 AM

Hi rhysa,

It would help if you could provide the correct result manually and then I will try to achieve them using formula. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**rhysa** · 02-21-2012, 11:26 AM

The correct value should be any date that isn't specified. I have worked that bit out, no issue there. The bit I can't work out how to do check if 6060227 has also been schedule for the same dare as 6132932, 6134903, 6103214, 6100233 and 6114796 as they are listed as wanting to sit the exam together. Don't worry about the exclusion date bit, I have the formula to check that.

Thanks!

**dilipandey** · 02-22-2012, 02:56 AM

Hi Rhysa,

See the green area in attachment where I have given the solution to your specific query. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>