How do I compare two dBs(spreadsheets) based on multiple criteria?
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!!! |
How do I compare two dBs(spreadsheets) based on multiple criteria?
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!!! |
How do I compare two dBs(spreadsheets) based on multiple crite
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!!! |
All times are GMT +1. The time now is 03:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com