How do I compare two dBs(spreadsheets) based on multiple criteria?

[Zac-Diggity]

Ok, so here's the rundown...

2 seperate databases (and by db's I mean spreadsheets)... one dB has the
information for the start time/end time/date/location of medical
staff(nationwide). The second dB is a nation wide index of all of the
accidents that lists time/location/date and other remarks. What I am looking
to do is make 1 cell(on the accident dB) that references the medical staff dB
and tells me if the accident occured between the hours that a medical staffer
was on duty. A simple Yes or No is all that I'm looking for, but it's gotta
match the date, city, and reference between the work hours. The staff dB is
expected to be around 3000 lines and the accident db around 5000. Please Help
me!!!

[Toppers]

Assume Sheet1 (Medical Staff) has the following:

Col A : Start time
Col B : End time
Col C : Date
Col D : Location

Sheet2 (Acccidents):

col A : Accident Time
Col B : Location
Col C : Date

In Col D (Sheet2 - and assuming data starts in row 2) put:

=IF(SUMPRODUCT(--(Sheet1!D2:D20=B2),--(Sheet1!C2:C20=C2),--(A2>=Sheet1!A2:A20),--(A2<=Sheet1!B2:B20))>0,"Y","N")

and copy down.

Obviously adjust ranges to suit your requirement.

HTH

"Zac-Diggity" wrote:

> Ok, so here's the rundown...
>
> 2 seperate databases (and by db's I mean spreadsheets)... one dB has the
> information for the start time/end time/date/location of medical
> staff(nationwide). The second dB is a nation wide index of all of the
> accidents that lists time/location/date and other remarks. What I am looking
> to do is make 1 cell(on the accident dB) that references the medical staff dB
> and tells me if the accident occured between the hours that a medical staffer
> was on duty. A simple Yes or No is all that I'm looking for, but it's gotta
> match the date, city, and reference between the work hours. The staff dB is
> expected to be around 3000 lines and the accident db around 5000. Please Help
> me!!!
>
>

[Zac-Diggity]

Toppers... you rock! Thank your very much. I would have never guessed
SUMPRODUCT.



"Toppers" wrote:

> Assume Sheet1 (Medical Staff) has the following:
>
> Col A : Start time
> Col B : End time
> Col C : Date
> Col D : Location
>
> Sheet2 (Acccidents):
>
> col A : Accident Time
> Col B : Location
> Col C : Date
>
> In Col D (Sheet2 - and assuming data starts in row 2) put:
>
> =IF(SUMPRODUCT(--(Sheet1!D2:D20=B2),--(Sheet1!C2:C20=C2),--(A2>=Sheet1!A2:A20),--(A2<=Sheet1!B2:B20))>0,"Y","N")
>
> and copy down.
>
> Obviously adjust ranges to suit your requirement.
>
> HTH
>
> "Zac-Diggity" wrote:
>
> > Ok, so here's the rundown...
> >
> > 2 seperate databases (and by db's I mean spreadsheets)... one dB has the
> > information for the start time/end time/date/location of medical
> > staff(nationwide). The second dB is a nation wide index of all of the
> > accidents that lists time/location/date and other remarks. What I am looking
> > to do is make 1 cell(on the accident dB) that references the medical staff dB
> > and tells me if the accident occured between the hours that a medical staffer
> > was on duty. A simple Yes or No is all that I'm looking for, but it's gotta
> > match the date, city, and reference between the work hours. The staff dB is
> > expected to be around 3000 lines and the accident db around 5000. Please Help
> > me!!!
> >
> >