Duplicates find / delete

[Manolocs]

Hello, I have this extensive two columns list, many of the items in the list are duplicated is there a way to find and delete the complete row of each one of the duplicate in both columns?

 

[hiker95]

Manolocs,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, PNG/IMG file, or, flat text) try one of the following:

There are several methods. Here are 3 for you to investigate.

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

To test the above:
Test Here

Or, you can post your workbook/worksheets to the following free site, and provide us with a link to your workbook:

https://dropbox.com

 

[Manolocs]

@Hiker95, thank you for your reply and your time, sorry if is missing some value information, I have both Mac and Windows the spreadsheet below is saved in Mac.
https://dl.dropboxusercontent.com/u/14313867/FilterDeleteRows.xlsm

 

[hiker95]

Manolocs,

Thanks for your workbook/worksheet.

Here is a macro solution for you to consider, that does not do any looping in your raw data rows, and, uses columns C, and, D, as a work area.

In column C, beginning in cell C3, I am concatenating A3 and B3.

In column D, I am putting a countif formula that will result in an error #N/A if the count is greater then 1. And, using specialcells, we delete any row in column D that contains the error #N/A.

Your raw data's last used row is 835. After the macro the last used row is 437.

I have no experience with a Mac, but, the macro is nothing fancy, and, you should give it a try in a copy of your workbook.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub DeleteDuplicates()
' hiker95, 11/09/2015, ME900715
Dim lr As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("C3:C" & lr)
    .Formula = "=A3&B3"
    .Value = .Value
  End With
  On Error Resume Next
  With .Range("D3:D" & lr)
    .Formula = "=IF(COUNTIF($C$3:C3,C3)>1,#N/A,"""")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete xlUp
  End With
  .Range("C3:D" & lr).ClearContents
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteDuplicates macro.

 

[Manolocs]

@hiker95 Very sorry for the delay, your Macro works perfect, I had to try in Windows version, because in Mac version, Excel crashes when running, probably is because I had 25K rows, in Windows version, the result came at 14K but I am still have many differences to clean manually, some of them because the cells are not identical, commas, spaces etc. Anyway help a lot and performed very well, Thank you very much save me a lot of work.

 

[hiker95]

in Mac version, Excel crashes when running

Manolocs,

I have no experience with a Mac.

help a lot and performed very well, Thank you very much save me a lot of work.

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

I had 25K rows, in Windows version, the result came at 14K but I am still have many differences to clean manually, some of them because the cells are not identical, commas, spaces etc.

Can I see your actual 25K rows workbook/worksheet that runs on a PC?

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[Peter_SSs]

Does this also work for you?

Sub NewList()
  Range("A2:C2").Formula = Array("Name", "Number", "=COUNTIFS(A$2:A3,A3,B$2:B3,B3)=1")
  Range("A2", Range("B" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("C1:C2"), CopyToRange:=Range("E2"), Unique:=False
End Sub

 

[Peter_SSs]

.. or you may want this expanded code to highlight numbers for the second & subsequent identical names.

Sub NewList()
  Range("A2:C2").Formula = Array("Name", "Number", "=COUNTIFS(A$2:A3,A3,B$2:B3,B3)=1")
  Range("A2", Range("B" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("C1:C2"), CopyToRange:=Range("E2"), Unique:=False
  With Range("F3", Range("F" & Rows.Count).End(xlUp))
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=E3=E2"
    .FormatConditions(1).Font.Color = vbRed
  End With
End Sub