12

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.

Improve this question
0

4 Answers 4

Reset to default
20

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

enter image description here

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).

enter image description here

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

enter image description here

Improve this answer
4

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 :)

Improve this answer
2

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.
Improve this answer
0

Another easy way to do this would be to have the duplicates highlighted in the column, then turn on the filter option & sort the column by cell color. The duplicates will all be sorted by cell color at the top - everything below that isn't highlighted in the color you picked for duplicates will then be easy to delete. It's a work-around but it's quick and easy.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .