+ 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
    75

    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 Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,383

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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 2019
    Posts
    17,428

    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
    75

    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 2019
    Posts
    17,428

    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