How to remove non-duplicate records in Excel-2010

How do I 'Remove Non-Duplicates' keeping only the duplicate records? I know I can highlight duplicate records with a different color, but I'd prefer to remove the non-duplicates instead, as I don't want to scroll though thousands of records looking for the needle in the haystack.

I want to keep the duplicates and remove the slingletons.

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.