+ Reply to Thread
Results 1 to 8 of 8

Delete duplicate rows where Event ID and part_number matches. Keep only unique records.

  1. #1
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    Delete duplicate rows where Event ID and part_number matches. Keep only unique records.

    Hi,

    I need help in regards to having an Events ID column (column A). Here I have multiple ID's some unique, some duplicates.

    However, from this I want to delete any rows where BOTH Event ID and part number (column B) match. From the example from my below image and attached spreadsheet. Row 2 and 3 match in regards to the Event ID and part number, therefore, I want to have row 3 deleted and only keep the unique rows.

    I also want to keep my other columns (C, D, E, F etc).

    How can I do this? Do I use Conditional Formatting or do I use a formula?

    Please note I hate approximately just over 50,000 rows! So I think a formula may crash this!

    Please help

    Capture10.PNG
    Attached Files Attached Files

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,036

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    Have you tried the Remove Duplicates feature ?

  3. #3
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    Hi Alfie92,

    Select all the cells in the range/table.
    On the Data Tab select Remove Duplicates
    Then click Unselect All
    Then "tick" Event ID and Part Number
    Then click OK

    Any duplicates will be removed.

    Regards

    peterrc

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,057

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    You can't actually delete rows using a formula.

    What you can do, though, is to use a formula to identify rows which need to be removed, and then you can EITHER use Autofilter to delete the offending rows in one operation, OR use some other formulae to copy the data for the rows that you want to keep into a different area of the sheet (or into another sheet entirely). If you take this latter approach, you will need to fix the values, and then you can delete the original data, so there is a bit more manual manipulation of the data.

    Here is an appropriate formula to use in cell H2 which will help in either approach:

    =IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,MAX(H$1:H1)+1,"-")

    Copy this down to the bottom of your data, and you will see a series of numbers and hyphens down the column - the numbers are for the rows that you want to retain, and the hyphens indicate rows that you want to delete.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    01-09-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    27

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    Hi, thanks for this but in the column where the formula is included, it only returns a 1, even in the cells where it should return a hyphen. Please help?
    Last edited by AliGW; 02-05-2020 at 05:24 PM. Reason: Please donít quote unnecessarily!

  6. #6
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    470

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    I think that VBA will be a very good solution for your problem. Try.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by maras_mak; 02-05-2020 at 12:32 PM.
    Best Regards,
    Maras.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,057

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    Quote Originally Posted by Alfie092 View Post
    ... it only returns a 1, even in the cells where it should return a hyphen. Please help ...
    Perhaps you missed out some of the $ signs in the formula.

    I've attached the file I worked on, with the formula in H2 copied down.

    Hope this helps.

    Pete
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,389

    Re: Delete duplicate rows where Event ID and part_number matches. Keep only unique records

    Try the attached, it leaves filtered Sheet2
    Note it leaves one unique of each duplicate still in the file.
    at present sheet2 is blank.
    Attached Files Attached Files

+ 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] [SOLVED] Macro to delete unique rows marked and remove 2nd duplicate row
    By SellMeTv2 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-29-2018, 09:03 PM
  2. [SOLVED] Delete entire Rows if Column value is unique OR Duplicate is less than three
    By analystbank in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2014, 12:09 PM
  3. [SOLVED] How to delete duplicate records using criteria for multiple rows
    By Ardiko in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-31-2013, 04:39 AM
  4. Replies: 6
    Last Post: 04-19-2011, 12:15 PM
  5. Macro To Delete Duplicate Records (Rows), While Omiting Specific Columns
    By Orangeworker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2010, 12:37 PM
  6. assigning unique id's to duplicate records
    By stevefsm in forum Excel General
    Replies: 5
    Last Post: 01-13-2009, 09:28 AM
  7. Replies: 2
    Last Post: 01-13-2005, 04:06 PM

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