Identify Duplicates

[blandow]

Hi,

I have a simple task that is becoming difficult. I have two lists of phone numbers. One is from a text file, and one is from an excel spreadsheet.
I put all the data in one column and used the Conditional Formatting tool to color fill the duplicates. I then used the sort/filter to try to sort by color, bringing the duplicates (colored cells) to the top. The problem is that there are almost 900,000 records and my system cant seem to handle it. Its a 2.6ghz machine w/2gb ram running Vista. Any help would be great.

[ngay528]

If I understand your task the following seems to be easier:

Sort phone numbers
Run macro through each cell by checking the next (or previous) cell to see if it matches, if so, delete (or mark) it.

if you are trying to move all duplicates to the top, then instead of deleting it, either put a letter before the number or mark the cell next to it then after going through all the cells sort by the next column.

[blandow]

I was hoping there was a macro out there already that would due this task. Would Access be a better tool?

[Leith Ross]

Hello blandow,

Can you post a workbook with some of the data in Excel 2003 format? I don't have Excel 2007 or Vista.

[ngay528]

In addition to my suggestion above, You could also create a pivot table with your list creating numbers and quantity. Only takes three or so commands and can easily see which numbers have duplicates. Easiest way would be to record a macro of yourself doing it.

But I guess I am not sure what your overall goal is in the end.

[Palmetto]

How about using Advanced Filter and set it to copy unique values to a new location?

If necessary, you could break your data into two or three chunks and process them sequentially.