+ Reply to Thread
Results 1 to 3 of 3

compare duplicate and retain only latest one

  1. #1
    Registered User
    Join Date
    10-27-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    13

    compare duplicate and retain only latest one

    HI Team

    I have quite a tricky one. I have a list of values which also has duplicates. I want to remove the duplicates but after comparing it to the dates. Retain the latest date and remove the old dates
    Eg. there are two duplicates, one has a date of 31st Jan 2013 and the other one has a date of 25 jan 2013. I want to remove the duplicate which has the older date ( 25 Jan 2013) and keep the one which has the latest date. Can anyone help....its very urgent. Im stuck with a report.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: compare duplicate and retain only latest one

    hi nigel. maybe try this formula in C2 & copy down:
    =AND(SUMPRODUCT(MAX(($A$2:$A$200=A2)*$B$2:$B$200))=B2,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2))

    then autofilter those that are FALSE & delete them

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    10-27-2011
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: compare duplicate and retain only latest one

    cool..works fine...

+ 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: 9
    Last Post: 11-28-2013, 05:20 PM
  2. [SOLVED] Compare Rows, Delete duplicate values, Sum values but Retain blank rows
    By chancw in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-24-2013, 07:38 AM
  3. Remove duplicate address rows but retain spouse name
    By dsrinssyb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-24-2013, 03:57 PM
  4. Check duplicate then compare latest version then give a value
    By henkdevreisch in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2013, 07:23 AM
  5. Delete duplicate rows and retain only one
    By canada123 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2010, 07:40 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