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

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

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

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

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