Delete Duplicates

[Hillinto]

I have a list of 18k rows, and about 7.5k are known duplicates. The delete duplicates button will not delete the duplicates (except it keeps returning 41 duplicates for an unknown reason, but there definitely thousands more it is missing). I tried the remove duplicates button, advance sort, coloring cells, and some macros. The list is the combination of two lists, one older one just compiled, I was able to seamlessly delete dups in another list.

Why is Excel not recognizing my duplicates with the remove duplicates button or any other method??? Is there any solution to this problem?



Patrick

[Andy Pope]

Are the items really duplicate?

If the cells are text make sure there are not leading or trailing spaces.

If the cells are numeric make sure the values are the same. Perhaps the values have a number format that only makes them look similar.

Otherwise post a small example with some duplicates that you think should be deleted so we can see exactly what is happening.

[Hillinto]

Thanks Andy for your feedback. I went back in and tried changing the format of the entire spreadsheet without any success. And I doubled checked that there aren't any spaces, etc. So you know, the master spreadsheet I am working on was made from two separate spreadsheets.... one of which originated out of Excel 2003, and now are both in Excel 2007. However, I converted the spreadsheet to a 07 workbook, and it is now showing a .xlxs extension. I am completely out of ideas.

Thank you so much for your feedback.

I've attached a small sample of the duplicates; please let me know anything else I can provide.

Patrick

[shg]

To pick an example, A3 contains some text (Gerard), and A4 contains the same text followed by a line feed.

Ditto for A2 and A5, and other apparent duplicates.

Assuming none of those belong, select the entire range and do Ctrl+H. In the Find box, press and hold the Alt key, and on the NUMERIC keypad, enter 0010

Leave the Replace box blank, and press Replace All.

[Hillinto]

Shg & Andy you guys are GOLD!!! That worked!!

Shg, how did you you see the line feed?

Thanks,

Patrick

[teylyn]

Shg, how did you you see the line feed?

.... he's seen it all before!

[shg]

Shg, how did you you see the line feed?

I have an add-in that displays cell contents in binary. It's posted in the first post in this thread: http://www.excelforum.com/tips-and-t...sonal-xls.html

[meggles]

I'm having a similiar problem with the attached file. (i've tried advanced filter, remove duplicate, pivot table, color conditional sorted, if functions) when I attempt the fix shg & teylyn suggested above, I cannot get the 'NUMERIC' keypad to appear in 'Find' Function.

1- Can anyone please let me know if I have the same problem as Hillto had?
2- If not, any idea what the problem is?

thanks in advance!

[teylyn]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[meggles]

Apolgizes for the mis-step and thank you for correcting me. Cheers.