Moving Duplicates

Posted by Brennan M on January 04, 2002 7:52 AM

Hello,

I have a sheet with around 3000 entries and 15 column headings. Some of the entries are duplicates based on data in one column. I need to be able to move all of the duplicate entries EXCEPT for one (if there are 8 duplicates, I need to move 7) from sheet 1 to sheet 2. Or, if there is a conditional format I can use to highlight the duplicates and move them manually, that would work as well. Suggestions? Thanks in advance!

Brennan

Posted by Tom Urtis on January 04, 2002 10:10 AM

OK, I'll warn you up front this is a cumbersome-looking solution but I think it does the trick:

Assuming your range is A2:M3000, with A1:M1 as a header row,
And column D is the one containing duplicates

(1) Sort your range by column D
(2) In N1, enter Quant (optional)
(3) In N3, enter =IF(D3=D2,N2+1,0) Note, keep N2 blank
(4) Copy down this formula to N3000 (last row of your range)
(5) Copy column N, Paste Special Value over itself
(6) Select A1:N3000, Data > Filter > Auto Filter
(7) Choose Custom, and enter the criteria Is greater than or equal to 1 AND is less than or equal to 7
(8) Click Edit > Go > Special > Visible cells only
(9) Click Edit > Copy, click on the first cell of the range in whatever worksheet you want, and click Edit > Paste.
(10) Hit Esc, go back to your original sheet, hit Delete.
(11) Hilite A1:N3000 again, Data > Sort by any column, to get rid of the blanks.

One caveat in step 10, it may also delete your header row, which you can retrieve from your pasted page or make a copy of first, and then re-paste.

Any help?

Tom Urtis