Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5
Default Display only duplicate values and delete UNIQUE Items

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Display only duplicate values and delete UNIQUE Items

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.

"WYMMIY" wrote:

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?

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,365
Default Display only duplicate values and delete UNIQUE Items

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.

"WYMMIY" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for duplicate items and delete them chanse44 Excel Discussion (Misc queries) 4 April 2nd 08 01:06 AM
Return Unique Consecutive Duplicate Values across Single Row Sam via OfficeKB.com Excel Worksheet Functions 22 February 6th 07 12:44 PM
Return Unique Duplicate Numeric Values across Single Row Sam via OfficeKB.com Excel Worksheet Functions 7 January 21st 07 03:51 AM
Duplicate and unique items in 2 lists Vince Excel Discussion (Misc queries) 1 August 23rd 06 07:19 AM
counting unique items(values or text) guneet_ahuja Excel Worksheet Functions 11 August 22nd 06 07:52 AM


All times are GMT +1. The time now is 06:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"