MrExcel.com - Your One Stop for Excel Tips & Solutions

Locating and eliminating duplicate cells

Posted by Dick Riker on February 07, 2002 11:28 AM

Frequently I must copy a long list of 3 to 6 digit figures from a report that runs in another program into an Excel spread sheet for manipulation. The list of figures properly includes duplicates and even triplicats which, for my Excel purposes, I must eliminate (along with the entire accompanying row), leaving only one cell copy of each figure.

Any suggestions?

Posted by ctwigg on February 07, 2002 12:01 PM

Sort and subtotal by the number field, and hide the detail (click on the "2" level). Select the range of numbers, then Edit/Goto/Special/Visible Cells only. Then Edit/Copy and Edit/Paste to a new area. You will have to get rid of the "Total" labels attached to each number using a text formula. Or use a Pivot Table to get one row for each number, and copy/paste that.