+ Reply to Thread
Results 1 to 7 of 7

Removing duplicate dates from multiple cells

  1. #1
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    11

    Removing duplicate dates from multiple cells

    Hi all,

    I have a long list of cells that have dates and times in them with the format of '6/1/20 9:15:30 AM' etc. There may be multiple cells with the same date but different times. I am trying to get the earliest time stamp of these duplicate dates. For example:

    Please Login or Register  to view this content.
    I want the earliest 6/3 and 6/5. Caveat here is I have a column of data to the left that needs to be maintained for VLOOKUP purposes (as you'll see in the attached file). Not sure how to accomplish this or if it is even possible - the 'remove duplicates' option I don't think can apply here.

    Thanks!
    Last edited by cph020283; 07-02-2020 at 11:33 AM.

  2. #2
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,624

    Re: Removing duplicate dates from multiple cells

    Please try at
    F3
    =UNIQUE(CHOOSE({1,2},A3:A51,INT(--B3:B51)))

    H3
    =AGGREGATE(15,6,B$3:B$51/(INT(B$3:B$51)=G3)/(A$3:A$51=F3),1)

    or Power Query
    Please Login or Register  to view this content.
    Last edited by Bo_Ry; 07-01-2020 at 02:14 AM.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: Removing duplicate dates from multiple cells

    Administrative Note:

    @Bo_Ry

    Please take a moment to add code tags around the M Code. Thanks!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Removing duplicate dates from multiple cells

    Your F3 / H3 suggestion both give me errors - #NAME? for F3, and #NUM! for H3. Could it be something I am doing wrong?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,884

    Re: Removing duplicate dates from multiple cells

    I imagine that both errors stem from the use of the UNIQUE function and that someone more well versed in how the subscription version (365) would need to tell you how you could get it.
    If you don't have it, that would account for the #NAME in F3. Since the formula in H3 references the result in F3, H3 results in #NUM.
    Did the Power Query proposal work for you?
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    06-18-2020
    Location
    Arizona
    MS-Off Ver
    Office 365
    Posts
    11

    Re: Removing duplicate dates from multiple cells

    We found another solution to make this work so I didn't get a chance to try the Power Query. However, now I am interested in Power Query!

    Thanks all!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,884

    Re: Removing duplicate dates from multiple cells

    Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Replies: 4
    Last Post: 12-05-2019, 11:02 AM
  2. Need Help with removing duplicate values from multiple colums.
    By whooper in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-11-2016, 06:56 AM
  3. Removing duplicate words in individual cells
    By ypatel1990 in forum Excel General
    Replies: 1
    Last Post: 08-08-2014, 02:12 PM
  4. [SOLVED] Removing Duplicate Dates on X-axis
    By ternt in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-02-2012, 02:33 AM
  5. Replies: 1
    Last Post: 01-09-2012, 06:31 PM
  6. Removing duplicate rows when cells are identical
    By cynthia74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2011, 11:38 AM
  7. Removing duplicate cells
    By Belter in forum Excel General
    Replies: 3
    Last Post: 06-26-2007, 01:37 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