PDA

View Full Version : To identify the duplicate rows and copy the unique rows alone from sheet1 to sheet2



mvandhu
09-03-2011, 04:21 AM
Need VBA code for the below mentioned scenario.
scenario:

I have 3 columns ID1, ID2, Name in sheet1 of my excel workbook.
Based on ID1 and ID2, my duplicate rows should be removed ie. the unique rows should be identified and the unique rows should be copied to my sheet2 (retain the 1st duplicate row).

For Example

Sheet1:

ID1 ID2 Name
1 a Jack
2 b Rose
1 a Emily
1 b Jill
2 b Jeni


So in the example above I want to retain my 1st duplicate row in my sheet 2.
ie in sheet 2 my result should be as

ID1 ID2 Name
1 a Jack
2 b Rose
1 b Jill

The rows count always varies in my sheet 1.
Please help me out in this.
Since am new to this Macros it will be better if you could put comment lines for each and every step of code on what the code is doing.

Also need another code to retain the last duplicate row.

Example
In shee1:

ID1 ID2 Name
1 a Jack
1 a Rose
2 b Jill
1 a Will

In sheet2, my result should be as

ID1 ID2 Name
1 a Will
2 b Jill

marreco
09-03-2011, 05:36 AM
Good morning!!

Try this exemple to copy and paste.
Sub CopiarParaPlanilha2()

'copia da Plan 1 para plna 2, uma informação embaixo da outra. na coluna "A".
Dim lr
lr = Sheets("Plan1").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Plan1").Select
Range("A1:A" & lr).Copy
Sheets("Plan2").Select
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

this eliminates duplicate values..

Sub DeletarDuplicados()
Dim i As Integer
Dim Entrada As Range

Set Entrada = Application.InputBox(Prompt:="Selecione a coluna em que serão eliminadas as repetições", Title:="Eliminação de valores repetidos", Type:=8)

For i = Cells(Rows.Count, Entrada.Address).End(xlUp).Row To 1 Step -1
If Application.WorksheetFunction.CountIf(Range(Entrada.Address), Cells(i, Entrada.Address)) > 1 Then Rows(i).Delete
Next i

End Sub

mvandhu
09-03-2011, 05:42 AM
Hi,
Thanks for your response.
It asks for input from the user. i don't want that. And i want a single code to identify the duplicate rows and copy an paste the unique rows to another sheet. Not as 2 seperate codes as you have mentioned above..