How do I check for duplicates in an Excel file, in one row?

Question

I have one column of 19,000 entries. I need to remove duplicate entries from the Excel file. How do I use Excel to remove the duplicates from the list?

Answer 1

You may be trying to hammer a nail with a screwdriver. Since you have so many records, this problem could best be solved with a database like Microsoft Access. In the help in Access, there is a description about how to delete duplicates in a table automatically.

This may be a good time to try to put your data into Access and use the power of databases for all your entering, reporting and storage needs, but if you are bound to using excel sheets for your data, there is a way for you to use Access to link to the Excel sheet, copy the records to a table that will only allow a single entry per value and then export an excel sheet, a new sheet with only your unique values. You then only need to delete or rename the original and rename the new Excel sheet to the original file's name.

It is a several step process that can be accomplished with macros or code in a module in Access, but here's an outline of the process:

Open Access
Do, File Get External Data, Link Tables.
Change the files of type to: Microsoft Excel.
Navigate to your file, then click link.
Make your choices in the wizard and make a note of the linked table name. It is usually the sheet name from the Excel workbook.
Ignore the linked table for the moment and create a new table
with a field name that is the same as the field name for the column of data you linked to. (Using design view)
Be sure when you create the field that you make it a Primary Key field. This is how it will reject the duplicates.
Save the table.
Click into Queries, do New, Design View, Add the table you linked from Excel. Change the query to an append query. Append to the table you created with the Primary Key. Drag your field name from the linked sheet down to the field list area and be sure it says it will append to the field name in the newly created table. Click the run button (!) Since there are duplicates, it will tell you there are duplicates and the dupes won't be appended. Tell it to run the query anyway.

Close the query and you don't have to save it. Click back to Tables. Select the newly created table and export it to Excel. Open the newly exported Excel sheet using Excel and you will find less records and all will be unique.

There are all kinds of fun things you can do with module code to make the process more user friendly and save time, but that is another subject for another question!

Answer 2

This is surprisingly simple:

Assume your 19,000 entries are in column A. In cell B1 type "1" and then copy this down all 19,000 rows so that every cell in column B has a 1 in it.

Give column A and B a header at the top then select all of your data. For this example we will call the headings "Data" and "Number".

Click on "Data" -> "Pivot Table and Pivot Chart Report"

When the dialogue box comes up, just click finish.

Excel will create a new sheet with a big box and a small dialogue box with your headings (Data and Number) in it.

Drag the word Data to the left-hand column of the big box and drag the word "Number" to the big main area of the big box.

You will now have a list of all of the unique data items on the left and the number of times they each occur on the right. It is now very easy to see and items which have more than one entry.

Answer 3

Hi write a formula in the adjacent column =(a2 -a1) if it is a number drag the formula to apply to all rows in that column if the numbers are identical then the result would be zero if it is a text use a compare function. hope it resolves the issue

Answer 4

That is called conditional formatting

ITS easy watch the video now at: http://mistupid.com/tutorials/excel/ click condional formating link on number2

Answer 5

http://office.microsoft.com/en-us/assistance/HA011366161033.aspx