Thursday, October 30, 2008

Excel Duplicate Finder

After experiencing issues with some of my spreadsheets being rejected upon database importing due to duplicates, i decided to create a macro to make the solution simpler. The macro that i created, is contained within the excel file "excel_dupes_range_Value.xls" and can be easily applied to any spreadsheet you are working on.


1. Simply download this file, and open it in excel. Make sure to allow macros in the security dialog. In order to reduce confusion, i hid this workbook, so don't be alarmed when nothing new pops open - you will be able to tell if it is open in the next step.

2. The macro searches for duplicates only within the selection of cells. Select a group of cells (THIS IS CURRENTLY LIMITED TO DATA WITHIN THE SAME COLUMN).

3. In the toolbar go to Tools>Macro>Macros and you should see the "excel_dupes_range_Value.xls!findDupes" macro in the list of open workbooks. Select this and click "Run" -see example-

4. Once the macro is run, a new window will open up which displays all of the duplicate values. By double clicking on any value in the list, you will be taken to that value in the selection of data that you ran the macro on. If you click on the same value twice consecutively, it will take you to the second duplicate and so on. -see example-

Here is the duplicate findinding spreadsheet


The macro works by identifying the selection's range, copying the data within the range into th macro spreadsheet, sorting the data, and then looking for two identical values in sequence. A loop works through one cell at a time comparing the present and previous value. The loop runs until it reaches the end of the range selected by the user's selection. Once duplicates are found, the values are added to a listbox, and then the search is performed based on that value in the originating spreadsheet.


Anonymous Ashley said...

Thanks for the very informative post..I like to try these steps.

Keep continuing the good work..

February 4, 2010 2:11 AM  

Post a Comment

<< Home