Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for duplicate items and delete them | Excel Discussion (Misc queries) | |||
Return Unique Consecutive Duplicate Values across Single Row | Excel Worksheet Functions | |||
Return Unique Duplicate Numeric Values across Single Row | Excel Worksheet Functions | |||
Duplicate and unique items in 2 lists | Excel Discussion (Misc queries) | |||
counting unique items(values or text) | Excel Worksheet Functions |