How to remove non-duplicate records in Excel-2010

One way to delete unique entries will be to utilize a new column, using the formula below, to clearly label each entry as unique or not. Then you can filter the entire table, viewing only the unique values (as seen with a "TRUE" result from the formula below), and delete only those rows.

Put this in the top cell of a free column and fill it through the end of the column:

=COUNTIF(A:A, A1)=1

This assumes no header row, and that your data with dupes/unique values is in Column A.

Then you will want to turn filtering on for the column you just put the formula in. Ctrl+Shift+L in Excel 2013, or under the Data menu.

Note: There is a "6" in my cell A1

Then click the filter drop-down in the new TRUE/FALSE column and uncheck "FALSE" to show only uniques and click OK. Then Select the visible rows and delete those rows (right-click any row -> delete row).

Then just turn the filter back off (if it's still on) and you have all your dupes.

well it is easy ,

first you filter the duplicates (select the columns or rows you want to analyse --> home--> conditional formatting --> highlight cell rules --> duplicate values --> highlight red (ex)

second you select the whole columns and rows from tab in the upper left corner off the excel sheet

third you go to data then select filter

you go to the columns selected earlier(the ones you want to analyse and then filter (drop down box of the first raw of each column) and then select filter by color

now you have duplicates grouped together .. you can copy them to next excel sheet or remove unique easily

BR , A.Fouad :)

It's surprising that answers to similar questions usually assume one column to be checked for duplicates... Here's how to do it:

• Add a column with a unique ID (simply a number per row)
• Copy the whole sheet (the copy will keep all records)
• Use "remove duplicates" in one sheet, only deselecting the column with the unique ID
• You now have 1 sheet with only unique records and one with all.
• In the full sheet use a basic vlookup based on the unique ID to identify the unique records. Filter them out to see your duplicates only.