Deduped, A Little Magic To Clean Up Your Act

Excel is probably SMEs most used database and contacts manager system, and one of the tasks that is often performed is comparing lists and finding duplicate records. With increasingly stringent controls on data held by organisations undertaking this essential and highly efficient task will ensure you are confident in your data lists and eliminate duplication across lists.


Open your spreadsheet and make sure you know which columns you are comparing. For example, ‘Column A’ and our comparison lists are: ‘Sheet 1’ and ‘Sheet 2’ of the same worksheet.
Enter the following formula in cell B1 of Sheet 1. = COUNTIF(SHEET1A:A, SHEET2A:A). Hit Enter. If the record is unique (it doesn’t appear on both lists), this will evaluate to 0.
Copy the formula from cell B1 through as many rows as you need on Sheet 1.
Select columns A and B, and click on the Home tab (in Excel 2007) and select Sort and Filter. Choose to filter the results by Column B in ascending order. This will move all the results with a ‘0’ in column B to the top, putting all your duplicate records at the bottom of the list.
You can now delete he duplicate records and save the list with confidence.
If you have other questions that our team can help you with do not hesitate to contact us to discuss these Astec 01424 460721

Speak Your Mind