Display only duplicate values and delete UNIQUE Items

[WYMMIY]

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?

 

[JLatham]

If you can put up with using a helper column you won't need VBA to do this,
just a formula and Filtering.

Assume your data goes from row 1 to row 298 and that the data that can be
duplicated is in column C. In a helper column, put this formula in a cel in
row 1:
=IF(COUNTIF(C$1:C$298,C1)>1,1,0)
Fill the formula down to the end of the data at row 298. That will put a 1
into the helper column for all entries that have multiple entries
(duplicates) in column C. Unique entries will have a 0 in the helper column.
Then just apply data filtering to the helper column and by choosing to view
cells with 1 you'll display only the duplicated entries.

Hope this helps some.

 

[JLatham]

Brain not working well this morning - you also indicated you wanted to delete
the entries with UNIQUE entries. My solution using helper column and filter
works for that also. Just filter to show entries in the helper column with 0
in them and then select them all and delete them. Then unfilter and only the
duplicated entries will remain.