I think I understand what you want and, if I do, you need vb code which is pretty much in the realm of people who make fairly serious $$ doing programming work.
So - here's what I think you want. On sheet #1 there are, say, 1000 rows. Maybe 210 of them are duplicates. You want those 210 duplicates preserved and moved to sheet #2, and then deleted from sheet #1. Or is it that you want the duplicates gone and not preserved at all? It's a bit unclear from what you're asking.
The way that I would bang this out if I were doing this job would be as follows, more or less.
- Make a duplicate of sheet #1, call it, say, sheet #2.
- For sheet #1, create a pivot table (place it on sheet #3) and have it count instances of whatever you are checking on is. E.g . if you have a row with three values and you need all three of those values to be identical in order to call a row a 'duplicate', then you would need to pivot on all three values.
- For the pivot table, filter it so that you are only seeing anything with a count of >1
- If you don't want to preserve the duplicates, you would delete from the originating sheet (e. g. sheet #1). Personally, I would do it manually but that's mainly because I am not much of a vb coder. I would also periodically refresh the pivot table on sheet #3 in order to assure I wasn't deleting anything I didn't want gone.
- If you do want to preserve the duplicates, I would also pivot on the copy on sheet #2 (onto sheet #4) and this time I would filter it so that I was only seeing anything with a count = 1. I would then delete everything else
- Once done, any sheets with just pivot tables could be deleted.
Or you could use upload the spreadsheet into MS Access as a table and run a duplicate delete query