Display only duplicate values and delete UNIQUE Items

[Mistry]

All

I have a very large list of data and on a monthly basis i need to
display only the duplicate items in a spreadsheet. I would like to do
this in VBA and then run it as a macro on the spreadsheet. Alot of
the sites that i have seen only show how to removed the duplicates.
Excel 2007 has a function which removed all duplicates but so far i
have found nothing that only displays the duplciates.... any ideas
anyone?

 

[Pete_UK]

Assuming that the field you use to determine uniqueness is column A,
you can put this formula in a helper column:

=IF(COUNTIF(A:A,A2)>1,"Duplicate","Unique")

then copy down (assumes you have a header row). You can apply
Autofilter to this column and select Unique from the filter drop-down.
Then highlight all the visible rows and Edit | Delete Row. Choose All
from the filter pull-down, then delete the helper column.

Record a macro while you do this once (use relative addresses) and
then you can re-run it in the future.

Hope this helps.

Pete

 

[ThatSaid]

Don't use VBA.
keep your data in a external (i.e. "separate") file, an excel file or a CSV
type file will do.

Use a Pivot table to read that file, and use a filter that show only the
rows that have more than 1 entry.

Anything more than 1 is a duplicate.

When you save the pivot table file, don't save it as a normal excel fiel,
save it as a template (.xlt) and Excel will ask you if you want to empty the
data from template and refresh automatically from the data file, next time
you open the template. Reply "OK".


It's magic.

thatSaid