Can anyone change this VBA code has per my request

[Prasad K]

i have data in my sheet with duplicates in Col A Col B Col C Col D Col E i want to copy only unique values data from sheet1 to sheet2 has per same columns

i have found some vba code this code is working perfectly to me i have some small problem this below code is copying unique value data to Column F & my question is this code will copy unique data to sheet2 of Col A to Col E​

--------------------------------------------------------------------------------------------------------------------
Sub uniKue()
Dim i As Long, N As Long, s As String
N = Cells(Rows.count, "A").End(xlUp).Row
For i = 2 To N
Cells(i, 6) = Cells(i, 1) & " " & Cells(i, 2) & " " & Cells(i, 3) & " " & Cells(i, 4) & " " & Cells(i, 5)
Next i

Range("F2:F" & N).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
--------------------------------------------------------------------------------------------------------------------

 

[maabadi]

Try This:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
End Sub

 

[Prasad K]

Try This:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
End Sub

Thankyou so much it's working has per my request

And one more small help
I want to get count of duplicates in column F in sheet2 with column heading duplicate count

 

[maabadi]

You can use formula for This. For Example if your last cell at column F is Row 50 Then at F1 Write:

=SUMPRODUCT(1/COUNTIF($F$2:$F$50,$F$2:$F$50))

 

[Prasad K]

You can use formula for This. For Example if your last cell at column F is Row 50 Then at F1 Write:

=SUMPRODUCT(1/COUNTIF($F$2:$F$50,$F$2:$F$50))

Thankyou so much sir

 

[maabadi]

Sorry my fault this formula count only unique values then you should change formula to this to found duplicate count.

=CountA($F$2:$F$50) - SUMPRODUCT(1/COUNTIF($F$2:$F$50,$F$2:$F$50))

 

[maabadi]

Or Change Macro to this:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
  N = Ws2.Cells(Rows.Count, "F").End(xlUp).Row
  Ws2.Range("F1").FormulaR1C1 = "=COUNTA(R2C6:R" & N & "C6) - SUMPRODUCT(1/COUNTIF(R2C6:R" & N & "C6,R2C6:R" & N & "C6))"

End Sub

 

[Prasad K]

Or Change Macro to this:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
  N = Ws2.Cells(Rows.Count, "F").End(xlUp).Row
  Ws2.Range("F1").FormulaR1C1 = "=COUNTA(R2C6:R" & N & "C6) - SUMPRODUCT(1/COUNTIF(R2C6:R" & N & "C6,R2C6:R" & N & "C6))"

End Sub

Thank you sir for writen code again on my request & sorry code is working perfectly here is a problem it's getting count in column F is 0 only i am uploading screen shot
i want to get count of number duplicates if a employee details repeated in sheet1

 

[maabadi]

I misunderstood. try this formula at F2 and Drag it down:

=COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,B2,Sheet1!$C$2:$C$16,C2,Sheet1!$D$2:$D$16,D2,Sheet1!$E$2:$E$16,E2)

Or use this code:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
  Range("F2").FormulaR1C1 = "=COUNTIFS(Sheet1!R2C1:R" & N & "C1,RC[-5],Sheet1!R2C2:R" & N & _
  "C2,RC[-4],Sheet1!R2C3:R" & N & "C3,RC[-3],Sheet1!R2C4:R" & N & "C4,RC[-2],Sheet1!R2C5:R" & N & "C5,RC[-1])"
    Range("F2").AutoFill Destination:=Range("F2:F8"), Type:=xlFillDefault
    
  
End Sub

 

[Prasad K]

I misunderstood. try this formula at F2 and Drag it down:

=COUNTIFS(Sheet1!$A$2:$A$16,A2,Sheet1!$B$2:$B$16,B2,Sheet1!$C$2:$C$16,C2,Sheet1!$D$2:$D$16,D2,Sheet1!$E$2:$E$16,E2)

Or use this code:

Sub DelDupl()
Dim Ws1 As Worksheet, Ws2 As Worksheet, N As Long
  Set Ws1 = Sheets("sheet1")
  Set Ws2 = Sheets("sheet2")
  N = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
  Ws2.Range("A1:E" & N).Value = Ws1.Range("A1:E" & N).Value
  Ws2.Range("A2").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlNo
  Range("F2").FormulaR1C1 = "=COUNTIFS(Sheet1!R2C1:R" & N & "C1,RC[-5],Sheet1!R2C2:R" & N & _
  "C2,RC[-4],Sheet1!R2C3:R" & N & "C3,RC[-3],Sheet1!R2C4:R" & N & "C4,RC[-2],Sheet1!R2C5:R" & N & "C5,RC[-1])"
    Range("F2").AutoFill Destination:=Range("F2:F8"), Type:=xlFillDefault
   
 
End Sub

Not like this sir