Matching Different Dates with Same Item

In the attached spreadsheet, I'm trying to determine how long each individual animal stays at an animal shelter. The "Outcomes" worksheet contains the Animal ID# (Column A), which identifies each animal, the outcome date (Column C) and intake date (Column D) which I'm trying to match to intake dates in the "Intakes" worksheet (Column C). The standard index and match form works fine if an animal comes in only once. However, if an animal comes in more than once, the index and match functions pulls the same date since the animal has the same Animal ID# (Column F).

I'd like to sort both the outcomes and intakes in descending date order and then match the latest intake dates with the latest Animal ID # outcomes to properly calculate how long each animal stayed at the facility.

Any help would be most appreciated.

Hi
In order to undrstand you better can you please advise what intake date do you expect to see for A825492
(Luna) in lines 5 & 18 ?

OK so how about that?

please validate all the results to insure they bring the expected outcome:

Please Login or Register  to view this content.

Thank you, Belinda. That's perfect. It seems some of my intake dates I listed were after the outcome dates, but that was my error. The formulas you made work perfect! Really appreciate it!

Hi,
that was my assumption - that the intake date was the discharge and the outcome is the addmission,
so I'm surprised that the formula works. It is set to search intake > outcome.

I would advise you to check again, and let me know if need any amendments.....

Hi Belinda,

That was reversed. I should have had the outcome dates come after the intake dates.

I fixed the dates so the outcome dates happen after the intake dates. When I do that, the formulas contain "NUM" errors. Is there a way to fix? Thanks.

Hi,
Can you please fill in the intake dates manually so that I know what you are aiming for?

Thanks.

OK, your examples didnt make much sense (Luna addmitted on Nov. 04 and released on Dec. 30, and then admitted again on Nov. 05 and released on...Dec. 31st??)

But anyways this is the formula you probably need:
D2 and down

Please Login or Register  to view this content.

Ok, here is the spreadsheet with the desired results in Column I on the Outcomes worksheet. As you can see, I sorted the Outcome Dates (Outcomes worksheet)and Intake Dates (Intakes worksheet)in descending date order. The desired dates list the following:

1) Animal ID# that came in once = exact match (standard index & match function works)
2) Animal ID# that come in more than once = Latest outcome date >latest intake date > next latest outcome date >next latest intake date, etc.

Thanks again!

Thank you Belinda and Bebo. I finally had a chance to use this in my large spreadsheet and it works beautifully.

I was wondering if there is also a way I can pull another cell on the same row as the ones we pulled from the intake sheet? For example, I want to pull the "Age" (Column J) from the intake sheet on the same row as the Intake Date (Column C) and place it on the Outcomes worksheet. I've attached the file with desired Ages I'd like to extract. Thanks.

Hi
Try this in L2:
=INDEX(Intakes!$J$1:$J$19,AGGREGATE(15,6,(ROW(Intakes!$C$1:$C$19)/((Outcomes!B2=Intakes!$B$1:$B$19)*(Outcomes!D2=Intakes!$C$1:$C$19))),1))

Originally Posted by belinda200

Hi
Try this in L2:
=INDEX(Intakes!$J$1:$J$19,AGGREGATE(15,6,(ROW(Intakes!$C$1:$C$19)/((Outcomes!B2=Intakes!$B$1:$B$19)*(Outcomes!D2=Intakes!$C$1:$C$19))),1))

Works like a charm. Thank you!