• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find and move the duplicate values in another sheet


New Member

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



Excel Ninja
Hi ,

I think you need to explain what you want done with some examples ; what is your definition of duplicates , can you post from your uploaded file some data samples which are duplicates , and can you post examples of what should remain on Sheet1 , and what should appear on Sheet2.



New Member
in the attached file are duplicates

in sheet 1 to remain only the values that are their own matchless
the ones that are 2, 3, 4
to be moved

If a value is 2 times were replica found and modified spelling and basic value


New Member
try to change this code
the results will be placed on columns A and B in sheet2

Sub ertert()
Dim x, y(), i&, j&, t(), bu As Boolean
x = Sheets("Foaie1").Range("A2").CurrentRegion.Value
Redim y(1 To UBound(x), 1 To UBound(x, 2))
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 1 To UBound(x)
For j = 1 To UBound(x, 2)
If Len(x(i, j)) Then
If .Exists(x(i, j)) Then
t = .Item(x(i, j)): bu = True
y(t(0), t(1)) = x(i, j): y(i, j) = x(i, j)
x(i, j) = "": x(t(0), t(1)) = ""
.Item(x(i, j)) = Array(i, j)
End If
End If
Next j
Next i
End With
Sheets("Foaie1").Range("A2").Resize(i - 1, j - 1).Value = x
If bu Then Sheets("Foaie2").Range("A2").Resize(i - 1, j - 1).Value = y()
End Sub


Excel Ninja
Hi ,

I have run the macro , and I see that some data has been transferred to another sheet.

My question is what is your problem ? What do you want to do ? Can you explain ?



New Member
the code works, it is good

I want the results to be made in sheet2 in columns A and B not as it is now,the spread sheet


New Member
If you run the code that I put it with the file attached, you notice that the results are displayed in sheet2 found on multiple columns

I want results that are found not to be putin multiple columns

but only in column A and B whether there is more than one value, that I'm interested to know how many times you find a value that