Find and move the duplicate values in another sheet

Reply Wed 19 Feb, 2014 12:44 pm
Hello,Please help me

I have a file with many values, distributed across multiple columns.

From sheet1 i want to find and move all the duplicate values in the sheet 2

and I need a macro,a code macro to work at the level of the sheets, Sheet1-Sheet2

I want to move cut/paste all values duplicates 2 times 3 times or how many times is found
from sheet1, in sheet2 the results to be made in columns A and B

in sheet 1 to remain single value, only the values themselves which didn't pair
I mean if a value is 2 times
to move the original value
but and double found

Thank you
  • Topic Stats
  • Top Replies
  • Link to this Topic
Type: Question • Score: 1 • Views: 1,376 • Replies: 1
No top replies

Reply Wed 19 Feb, 2014 03:30 pm
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.
  1. Make a duplicate of sheet #1, call it, say, sheet #2.
  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.
  3. For the pivot table, filter it so that you are only seeing anything with a count of >1
  4. 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.
  5. 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
  6. 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.
0 Replies

Related Topics

  1. Forums
  2. » Find and move the duplicate values in another sheet
Copyright © 2024 MadLab, LLC :: Terms of Service :: Privacy Policy :: Page generated in 0.03 seconds on 06/16/2024 at 06:58:10