+ Reply to Thread
Results 1 to 16 of 16

Matching Different Dates with Same Item

  1. #1
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    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 ?

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    ----deleted-----
    Last edited by belinda200; 12-16-2020 at 02:43 PM.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    Re: Matching Different Dates with Same Item

    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!

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    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.....

  7. #7
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    Re: Matching Different Dates with Same Item

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

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

    Thanks.

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    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.
    Attached Files Attached Files

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,757

    Re: Matching Different Dates with Same Item

    Does this look like what you expect your solution to look like?

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    1
    ID # Name Intake Date Intake Date2 Address Type Condition Species *** Age Breed Color Column3 Outcomes.Outcome Dates
    2
    A825492 Luna
    12/4/2020
    12/4/2020
    Manor (TX) Owner Surrender Normal Dog Spayed Female 1 month Labrador Retriever Mix Black
    12/2/2020
    3
    A825492 Luna
    11/5/2020
    11/5/2020
    8102 Wolf Lane in Travis (TX) Stray Normal Dog Intact Female 4 weeks Labrador Retriever Mix Black
    12/2/2020
    4
    A825653 Mia
    11/8/2020
    11/8/2020
    7201 Levander Loop in Austin (TX) Abandoned Normal Dog Intact Female 3 years Beagle/Chihuahua Shorthair Tan/White
    11/10/2020
    5
    A825653 Mia
    11/16/2020
    11/16/2020
    Austin (TX) Owner Surrender Normal Dog Spayed Female 3 years Beagle/Chihuahua Shorthair Tan/White
    11/10/2020
    6
    A825700
    11/19/2020
    11/19/2020
    Austin (TX) Owner Surrender Normal Dog Neutered Male 3 months Plott Hound Mix Brown Brindle/White
    12/1/2020
    7
    A825700
    11/9/2020
    11/9/2020
    11220 Joseph Clayton Drive in Austin (TX) Stray Normal Dog Intact Male 2 months Plott Hound Mix Brown Brindle/White
    12/1/2020
    8
    A825700
    12/4/2020
    12/4/2020
    9500 Dessau Rd in Austin (TX) Owner Surrender Normal Dog Neutered Male 3 months Plott Hound Mix Brown Brindle/White
    12/1/2020
    9
    A825863 *Poppet
    11/14/2020
    11/14/2020
    15204 Eilers Road in Travis (TX) Stray Normal Dog Intact Female 8 months Labrador Retriever Mix Brown/White
    11/16/2020
    10
    A825863 *Poppet
    11/19/2020
    11/19/2020
    Austin (TX) Owner Surrender Normal Dog Spayed Female 8 months Labrador Retriever Mix Brown/White
    11/16/2020
    11
    A826166 *Ophelia
    12/1/2020
    12/1/2020
    Austin (TX) Owner Surrender Normal Dog Spayed Female 2 years German Shepherd Black/Brown
    11/28/2020
    12
    A826166 *Ophelia
    11/20/2020
    11/20/2020
    1433 Bradbury Lane in Austin (TX) Stray Normal Dog Intact Female 2 years German Shepherd Black/Brown
    11/28/2020
    13
    A826770 Lexi
    12/5/2020
    12/5/2020
    8107 Manassas in Austin (TX) Stray Injured Dog Intact Female 10 years Lhasa Apso White/White
    FALSE
    12/3/2020
    14
    A826771
    12/5/2020
    12/5/2020
    Austin (TX) Stray Normal Cat Intact Male 4 weeks Domestic Shorthair Brown Tabby
    FALSE
    12/4/2020
    15
    A826772
    12/5/2020
    12/5/2020
    Austin (TX) Stray Normal Cat Intact Male 4 weeks Domestic Shorthair Brown Tabby
    FALSE
    12/1/2020
    16
    A826773
    12/5/2020
    12/5/2020
    Austin (TX) Stray Normal Cat Intact Male 4 weeks Domestic Shorthair Brown Tabby
    FALSE
    12/3/2020
    17
    A826774
    11/26/2020
    11/26/2020
    22914 Cabanna Rd in (TX) Stray Normal Cat NULL NULL Sphynx Unknown
    FALSE
    11/3/2020
    18
    A826777
    12/5/2020
    12/5/2020
    6405 Cir S Rd in Austin (TX) Stray Normal Dog Intact Female 4 months Pit Bull Mix Black/White
    FALSE
    12/1/2020
    Sheet: Merge1
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    Re: Matching Different Dates with Same Item

    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!
    Attached Files Attached Files

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,465

    Re: Matching Different Dates with Same Item

    Try in D2:
    Please Login or Register  to view this content.
    Drag down

    It is base on rank# of outcome date of specific ID, if it was, i.e, 2nd rank, then look for the 2nd rank in intakes.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    Hi
    Please see attached corrected formula in D2 and down:

    =AGGREGATE(14,6,(Intakes!$C$2:$C$18)/(($A2=Intakes!$A$2:$A$18)*(C2>Intakes!$C$2:$C$18)),1)

    Sorry for the confusion...
    Attached Files Attached Files
    Last edited by belinda200; 12-17-2020 at 05:35 PM.

  14. #14
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    Re: Matching Different Dates with Same Item

    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.

  15. #15
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,732

    Re: Matching Different Dates with Same Item

    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))

  16. #16
    Registered User
    Join Date
    12-10-2020
    Location
    Newark, New Jersey
    MS-Off Ver
    2013
    Posts
    6

    Re: Matching Different Dates with Same Item

    Quote Originally Posted by belinda200 View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VLOOKUP an item that has more than one matching value?
    By coupland in forum Excel General
    Replies: 6
    Last Post: 07-14-2018, 02:10 PM
  2. Extract the dates missing per item from a list of dates
    By Freelanx in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-10-2018, 01:08 AM
  3. Finding an item that isn't matching.
    By kylpr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-07-2017, 04:42 PM
  4. [SOLVED] lookup if matching item and between dates
    By Sherk in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2015, 12:04 PM
  5. [SOLVED] Formula needed - line item of unique item matching criteria
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 04-18-2015, 06:32 PM
  6. Replies: 5
    Last Post: 07-26-2011, 10:35 PM
  7. Replies: 9
    Last Post: 05-21-2011, 12:14 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1