Excel Nested If with Sumproduct - Compare Dates 2

[NJko4]

Have a six-column Excel worksheet that have approximately 26,000 records in columns A through C and 28,000 records in columns D through F. Columns A through C is the admit and discharge dates for the employees at Hospital 1 and columns D through F are the admit and discharge dates for employees at Hospital 2. (Note, the data is from two difference sources and I consolidated to one worksheet)

Obj: 1) Determine if the employees that were admitted to hospital 1 also had an admittance to hospital 2 and there were overlapping dates of service. For example, EmpID 1046 had an overlapping dates of service and would require additional review because the employee cannot be admitted two places within the same time frame!

Obj: 2) Determine if the employees that were admitted to hospital 1 and also to hospital 2 and there were no overlapping dates of service. For example, EmpID
1072 would fit this category.

Currently, For objective 1, I have experimented with a nested if statement in column "G" along the lines of
=If(AND(sumproduct((EmpID1=A2)*(EmpID2=A2),(E2>=B2,F2<=C2)),"Yes","No")).

So far, I haven't been successful.


The format of the data is similar to the following;

EmpID---AdmitDt---DischDt---EmpID---VisitBeginDt--VisitEndDt
1046----1/1/08----1/6/08----1032----1/2/08--------1/3/08
1052----2/1/08----2/15/08---1032----1/5/08--------1/6/08
1052----2/18/08---2/22/08---1046----1/5/08--------1/6/08
1061----1/15/08---1/17/08---1046----2/1/08--------2/2/08
1061----1/25/08---1/28/08---1052----1/15/08-------1/20/08
1072----1/12/08---1/15/08---1052----1/30/08-------2/5/08
----------------------------1061----1/26/08-------1/26/08
----------------------------1072----2/15/08-------2/17/08

Any insight as to a resolution is sincerely appreciated.

Thanks in advance.

 

[SkipVought]

Hi,

The first order of business ought to be to position your data properly. You have TWO SEPARATE TABLES and, as such, ought to be separated as distinct tables. The best way would be on two separate sheets.

I'd then use MS query to join the two faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NJko4]

Did take a look at using MS Query for the issue.

However, is it possible to use a combination of sumproduct and nested if statements to determine if a particular MS Excel cell with dates is before or after another date?

 

[SkipVought]

SUMPRODUCT will not return a LOCATION or OFFSET. It returns a SUM or a COUNT, based on the criteria.

You could take a look at the OFFSET function, using MATCH and COUNTIF.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[IngDep]

What I need to accomplish is to compare dates.

So far, I have the following that appear to be along the right path:

=SUMPRODUCT((D5=$A$5:$A$56000)*(E5>=$B$5:$B$56000)*(E5<=$C$5:$C$56000)*($G$5:$G$56000))

Note, in column G, I have "Yes" in rows 5 through 56000.

My objective is to display "Yes" in column H, if there is an overlapping date that indicate that the patient was in Hospital 1 and Hospital 2 at the same time!

Shouldn't the above sumproduct work? What am I missing here?

 

[SkipVought]

[tt]
=SUMPRODUCT((D5=$A$5:$A$56000)*(E5>=$B$5:$B$56000)*(E5<=$C$5:$C$56000)*($G$5:$G$56000))
[/tt]
What this formula says is,

for ROW 5
For every D5 value in the column A range there is a TRUE
Whenever a column B range has a value >= the E5 value there is a TRUE
Whenever a column C range has a value <= the E5 value there is a TRUE

When you have, in rows 5 to 56000, TRUE TRUE TRUE, then include the NUMERIC VALUE in that row in column G and SUM THEM ALL UP and that's the NUMERIC VALUE that this formula will return.

Now you can COUNT all the TRUE TRUE TRUE rows, like this...
[tt]
=SUMPRODUCT(--(D5=$A$5:$A$56000)*(E5>=$B$5:$B$56000)*(E5<=$C$5:$C$56000))
[/tt]

For instance, if your row range were 1 to 4...
[tt]
A B C
x 1/1/2008 3/3/2008
v 1/1/2008 3/3/2008
x 2/2/2008 3/3/2008
x 1/17/2008 2/1/2008
[/tt]
then
D5: x
E5: 1/5/2008
[tt]
=SUMPRODUCT((D5=$A$1:$A$4)*(E5>=$B$1:$B$4)*(E5<=$C$1:$C$4))[/tt]
will return a count of 2 2 Yesses, I guess.

And do you have DATA in all the rows 5 - 56000?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[NJko4]

Data is not in all of the rows 5 - 56000, Some of the rows between 5 and 56000 are blank.

 

[SkipVought]

Why do you have blank rows? Not a good thing!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[IngDep]

Okay. After several attempts, it appears that I can maybe accomplish using MS Query or MS Access.

The problem encountered with using Sumproduct was the calculation time was not acceptable!

 

[SkipVought]

Are NJko4 & IngDep, the same person???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]