+ Reply to Thread
Results 1 to 10 of 10

Find earliest date for unique user then copy data from another cell

  1. #1
    Registered User
    Join Date
    12-01-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    O365
    Posts
    5

    Find earliest date for unique user then copy data from another cell

    Hi all,
    I have a data file showing user interactions with our service and each time the user comes in we record what their key issue is. I am trying to find the best way to find the earliest entry for each user and then show the issue that was listed against this entry. I have tried using the MIN function in the field settings of a pivot table but it seems to show the MIN date for each time the 'issue' changes. The data looks like this below. Where the issue is listed as UNSPECIFIED I'd like to skip to the next data for that User that has an entry other than Unspecified. Hope that makes sense. Thanks.
    Name Date Issue
    Karen 44146 Other
    Stewart 44146 Employment
    Shirley 44146 Unspecified
    Karen 44145 Other
    Ian 44145 Other
    Colleen 44145 Other
    Caroline 44144 Disability Support
    Chloe 44145 Other
    Erica 44145 Other
    Cindy 44145 Other
    Kristy 44145 Unspecified
    Casey 44145 Other
    Brian 44145 Daily Living
    Bob 44145 Service Information/Navigation
    Melisa 44145 Unspecified
    Lesley 44141 Employment
    Stewart Unspecified
    Joseline 44139 Housing/Tenancy/Homelessness
    Lesley 44140 Unspecified
    Colleen 44140 Daily Living
    Karen 44140 Unspecified
    Karen 44139 Mental Health
    Shirley 44139 Employment
    Susan 44020 Family Functioning
    Stewart 44138 Employment

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Find earliest date for unique user then copy data from another cell

    It's best to always attach a sample file with mockup results. Please see the yellow banner at the top of the page. but to get the earliest date that is not Unspecified, you could try this:

    =MINIFS(B3:B27,A3:A27,G3,C3:C27,"<>Unspecified")

  3. #3
    Registered User
    Join Date
    12-01-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    O365
    Posts
    5

    Re: Find earliest date for unique user then copy data from another cell

    Thanks for your quick response Greg. Not sure if that will help me partly because I'm unsure what the G3 in the formula is doing. Hopefully I've attached a sample file that might make my explanation of what is needed easier to follow.
    The first step is to find the earliest DateOfInteraction (Col B) for each Person (Col A), then I need to pull out the PrioritySupportIssue corresponding to that date. Ideally, I'd like to end up with a list of unique individuals showing their PrioritySupportIssue. As noted, if the earliest DateOfInteraction has an UNSPECIFIED issue then skip to the next date for the person that does not have an UNSPECIFIED issue. I thought that using a Pivot table might be the best approach but can't get that working for me either
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find earliest date for unique user then copy data from another cell

    In F2
    Please Login or Register  to view this content.
    In G2
    Please Login or Register  to view this content.
    In H2
    Please Login or Register  to view this content.
    Pull down all.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    12-01-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    O365
    Posts
    5

    Re: Find earliest date for unique user then copy data from another cell

    Thanks for your reply but that is not extracting the earliest date for all individuals. For example, the earliest date for Karyn is 09/04/2020 (note this is in dd/mm/yyyy format). As the date is incorrect, the Issue is also incorrect. I also tried creating column F and G using pivot table and the MIN function and then using your column H but still no luck. Does it make it any easier if we don't try to exclude the UNSPECIFIED cells?

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Find earliest date for unique user then copy data from another cell

    Since you're on O365, you can use the dynamic array formulas.

    To get the unique names, enter this formula in E2:
    =UNIQUE($A$2:$A$551)

    To get the earliest date without "Unspecified", enter this in F2:
    =LET(a,MINIFS($B$2:$B$551,$A$2:$A$551,E2#,$C$2:$C$551,"<>Unspecified"),IF(a=0,"None",a))

    To get the Issue, in G2 you can enter this:
    =XLOOKUP(E2#&F2#,A2:A551&B2:B551,C2:C551,"Not found",0)

    See attached.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find earliest date for unique user then copy data from another cell

    Corrected formula for G2 then copied across
    Please Login or Register  to view this content.
    in the data the dates are sorted. If it is same in your file also this simple formula works.
    In K2 , copied across
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-01-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    O365
    Posts
    5

    Re: Find earliest date for unique user then copy data from another cell

    Unfortunately the UNIQUE and LET functions have not yet been rolled out to the O365 version we have operating here at work

  9. #9
    Registered User
    Join Date
    12-01-2020
    Location
    Perth, Western Australia
    MS-Off Ver
    O365
    Posts
    5

    Re: Find earliest date for unique user then copy data from another cell

    Thank you, it's close to perfect but I see that for individuals whose earliest interaction date was for an UNSPECIFIED issue and later interactions show a different issue, they do not get included in the outputs. I was hoping, for these individuals, to be able to look at the next interaction date until a non-UNPECIFIED issue is found. If not, then list UNSPECIFIED as the issue.

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Find earliest date for unique user then copy data from another cell

    Pl upload file showing the examples.

+ 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] sorting a unique list by earliest to latest date
    By cnak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2019, 02:28 PM
  2. Find which cell in each row has the earliest date/time
    By gcotterl in forum Excel General
    Replies: 12
    Last Post: 06-30-2017, 01:39 PM
  3. Find the earliest date in a range of data
    By juan.doe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2017, 04:52 AM
  4. Replies: 3
    Last Post: 02-10-2016, 07:26 PM
  5. [SOLVED] Returning Earliest and Latest Date for a Unique ID
    By chicity26 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2014, 09:39 AM
  6. [SOLVED] Access Query: Need unique values and earliest date.
    By AlphaSkidz in forum Access Tables & Databases
    Replies: 18
    Last Post: 02-09-2014, 07:41 AM
  7. Get Earliest Date of Each Unique Phone Number
    By gorgon1515 in forum Excel General
    Replies: 2
    Last Post: 10-11-2010, 09:16 PM

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