+ Reply to Thread
Results 1 to 4 of 4

Remove Duplicates with Dates

  1. #1
    Registered User
    Join Date
    08-14-2013
    Location
    D
    MS-Off Ver
    Excel 2010
    Posts
    17

    Remove Duplicates with Dates

    Hi,

    I have imagine 3 columns, id, start_date, end_date.

    ID contains duplicates so if a certain customer has multiple contracts, they will show up on list multiple times. I am trying to get a deduped list in most cases.

    Most contracts are like the following

    1, 1/1/2015, 12/31/2015
    1, 1/1/2016, 12/31/2016

    I can just dedupe the list, and do min array function and max array function to get the min and max start dates. I have this figured out. I need help with non-consecutive ones.

    ID, Start_date, End_Date

    2, 1/1/2015, 12/31/2015
    2, 5/1/2016, 12/31/2016
    2, 6/1/2017, 3/31/2018

    So in this case I can't dedupe b/c I cannot do min max since the contract is not all the way through the min and max dates.

    So I want to be able to identify these types of contracts through a IF statement or any other way so I can manually add these to the deduped list.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Remove Duplicates with Dates

    Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

    In attached Excel file try not to use:
    • merged cells
    • password protection
    • autorun VBA code
    • unnecessary formatting like: colours, borders, aligning another than default, etc...
    • unnecessary zooming/grouping/freezeing
    You have words to logically describe the problem

    To attach an Excel file to your post,
    • desensitize data
    • remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
    • click Go Advanced,
    • scroll down until you see Manage Attachments,
    • click that and select Browse,
    • select your file and click Open,
    • click Upload and you will see your attachment below Upload Files from a website
    • click Close this window,
    • click Submit reply

    After that you should see attachment in your post

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Remove Duplicates with Dates

    If all you need to do is find min and max dates, you could use this array...
    =max(if(ID=2,start-date))
    CSE
    The Min would be teh same, except you need to include >0
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-04-2017
    Location
    India
    MS-Off Ver
    2013
    Posts
    54

    Re: Remove Duplicates with Dates

    unable to understand your question.Please upload excel with the question and desired output

+ 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. Compare lists of URLs for duplicates, and remove duplicates
    By mrfranklin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2021, 02:54 AM
  2. Replies: 4
    Last Post: 08-30-2016, 10:18 AM
  3. Replies: 5
    Last Post: 01-13-2015, 01:42 PM
  4. [SOLVED] Remove duplicates and change dates - VBA
    By wrightyrx7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2014, 11:50 AM
  5. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  6. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  7. Replies: 2
    Last Post: 03-20-2011, 11:19 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