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

**Alfie092** · 02-05-2020, 11:26 AM

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

**Pepe Le Mokko** · 02-05-2020, 11:43 AM

Have you tried the Remove Duplicates feature ?

**peterrc** · 02-05-2020, 11:43 AM

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

**Pete_UK** · 02-05-2020, 11:55 AM

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

**Alfie092** · 02-05-2020, 12:18 PM

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?

**maras_mak** · 02-05-2020, 12:28 PM

I think that VBA will be a very good solution for your problem. Try.

Option Explicit

Sub OnyUniqueRows()
    Dim a(), rws
    Dim ms As String
    Dim i As Long
    Dim d As Object
    
    a = Sheets("Sheet1").[A1].CurrentRegion.Value
    Set d = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a)
        ms = a(i, 1) & a(i, 2)
        If Not d.exists(ms) Then
            d(ms) = i
        Else
            d(ms) = "a"
        End If
    Next
    rws = Filter(d.items, "a", False, 0)
    With Sheets("Sheet2")
        .UsedRange.ClearContents
        With .[A1]
            .Resize(1, 6) = Array("EventID", "part_number", "part_type", "description", "quantity", "days_affected")
            .Offset(1, 0).Resize(UBound(rws)+1, 6) = Application.Index(a, Application.Transpose(rws), Array(1, 2, 3, 4, 5, 6))
        End With
    End With
    Set d = Nothing
End Sub

**Pete_UK** · 02-05-2020, 12:48 PM

Originally Posted by Alfie092

... 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

**torachan** · 02-05-2020, 05:13 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] [SOLVED] Macro to delete unique rows marked and remove 2nd duplicate row

[SOLVED] Delete entire Rows if Column value is unique OR Duplicate is less than three

[SOLVED] How to delete duplicate records using criteria for multiple rows

Removing both duplicate AND original records? OR leaving only unique rows

Macro To Delete Duplicate Records (Rows), While Omiting Specific Columns

assigning unique id's to duplicate records

In column A I have duplicate records. How do I tag an unique reco.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions