+ Reply to Thread
Results 1 to 14 of 14

Identify duplicates and keep the latest based on latest

  1. #1
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Identify duplicates and keep the latest based on latest

    Hi,

    I'm trying to identify duplicates and keep the latest.

    The file contains two columns.
    First column is the Ticket_Id, which is unique.
    Second column is the instancia, which can be duplicate.
    third columns is where i want to identify the duplicates

    Each time a instancia enters to the system it give a new ticket. The system allows to enter same instancia, but if will give a new ticket (with a highir Ticket Id number).

    I would like to keep (mark as 1) the unique based on the ticket. And from the duplicates I would like to keep (or mark as 1) the unique base on its higher ticket, which means was the latest to enter to the system

    Ticket_Id INSTANCIA unique
    47483 401574944 1
    47485 401574966 1
    47486 401574972 0
    52427 401574972 1


    Sometime could be more that 2 duplicates (3, or 4)

    Any help will be really appreciated it.

    Thanks,
    mils
    Attached Files Attached Files
    Last edited by mils; 05-24-2020 at 03:55 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    C1 =IF(COUNTIF($B$1:$B$100,$B1)=Countif($B$1:$B1,$B1),1,"") and drag down.

    The red value is the last row of the range.

  4. #4
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Identify duplicates and keep the latest based on latest

    Hi oeldere,

    thanks for your replay. However, your solution does not take into account the Ticket_Id.

    from my example i would like to keep the instancia with the highest Ticket_Id. in this case the second one

    47486 401574972 0
    52427 401574972 1

    is that possible to do?

    thanks in advance!

  5. #5
    Registered User
    Join Date
    05-23-2020
    Location
    Guwahati, Assam, India
    MS-Off Ver
    2019
    Posts
    23

    Re: Identify duplicates and keep the latest based on latest

    I have tried to make out what you want. Please see the attached Excel Sheet. Let me know if anything I can do for you.
    Attached Files Attached Files
    Asha Kanta Sharma
    Guwahati, India

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    @mils

    Please show the file, where you get this failure, since I believe, my solution, is what you are asking for.

    That is why I ask for an excel file in #2, with the manualy expected results, so I can compare the result.

  7. #7
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Identify duplicates and keep the latest based on latest

    Thanks for your replay ashakantasharma.

    What I want is to populate the third column called unique, with 1 or 0.

    The formula should look first at duplicates Instancia (second column), when find one then look at the ticket_id (first column), and put 1 (in third column - unique), in the row where the ticket_id is higher (from both Instancia duplicates).

  8. #8
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Identify duplicates and keep the latest based on latest

    Hi @oeldere, I have attached the file. Hope it helps.

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    Why has C2 : C6 as result blanc?

    I add my offered solution in Column C and add your result in bleu background color in Column D.

    Please reply the criteria for C2 and C6.

  10. #10
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Identify duplicates and keep the latest based on latest

    Hi oeldere ,

    I'm a bit lost about your response.

    what is in my excel is not right. I want to put 1 on those duplicates with higher ticked_ID.

    I don't know how to apply your formula, sorry.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    You give in your excelfile as result for C2:C6 a blanc cell.

    I added my formula in your file and my result for those cells is 1.

    So there is a differance in what you expect to be the result and what I expect to be the result.

    In that case you have to explain to me, what the criteria are for the cells C2: C6 (to get the result blanc).

    If you add me the criteria, I will make the excel formula for that.



    edit

    you also have to change the range to 2083 (See the red text).

    C1 =IF(COUNTIF($B$1:$B$2083,$B1)=Countif($B$1:$B1,$B1),1,"") and drag down.

    I also added the amended file.
    Last edited by oeldere; 05-24-2020 at 04:58 AM.

  12. #12
    Registered User
    Join Date
    10-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    44

    Re: Identify duplicates and keep the latest based on latest

    the difference is that i would like to assign 1 to the one with the highest ticket_Id

    47486 401574972 0
    52427 401574972 1

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Identify duplicates and keep the latest based on latest

    Your comment in #12 my, formula will do that (to my opinion).

    Please open my Excel file I added and chech the results (#11)

    Yes I know, but when should the result be blanc (like you showed in C2:C6 of your file)?
    Last edited by oeldere; 05-24-2020 at 05:49 AM.

  14. #14
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Identify duplicates and keep the latest based on latest

    Another option in C2:
    Please Login or Register  to view this content.

+ 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] Identify latest records based on if logic
    By wisko in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-20-2019, 10:28 AM
  2. VBA- Filter Pivot table based on latest and 2nd latest date in column
    By ziyan89 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 12-29-2015, 11:18 AM
  3. Replies: 4
    Last Post: 05-23-2015, 12:08 PM
  4. Removing duplicates from a list based on the latest date
    By Krys14 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2014, 10:05 AM
  5. Replies: 9
    Last Post: 11-28-2013, 05:20 PM
  6. Powerpivot - Returning latest update based on latest date
    By Kehjz in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 08-22-2013, 02:45 PM
  7. Open Latest TextFile with latest TimeStamp
    By pr4t3ek in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-03-2009, 10:03 AM

Tags for this Thread

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