Search Duplicate and Result in Next Worksheet

[Vgabond]

Hi Guys

I need help to search duplicates in datas and the result will be in the next worksheet. Result will shows only one entry without anymore duplicate.

Regards
Vgabond

 

[Ketan]

@vgabond :

IF your data field(Say "agent") which may have duplicate, i suggest to creat the pivot table with the filed "agent" in row and the same in the data with count of "agent". This will provide you the agent code counts.(sort by descending and check the data).

To remove the duplicate from the main database, first sort the field "agent". Then create the temporary col next to "agent". Say "chk_field" in col "B".
Write the following formula in cell B2:
IF(A2=A1,"K"," ")( here col A has the data of agent.)
copy the formula in col B. Copy and paste the value and filter the "k" in col B. These will provide you the data those are duplicates. After deleting the filtered row, pls delete the col "B" - chk_field and you will find your data as per your requirement.

(The above can be recorded in macro too).

Share your experience.

 

[Vgabond]

You should thank to Erik
try

Option Explicit
 
Sub copy_uniques()
'Erik Van Geit (modified by jindon)
'070605
    Sheets(2).Columns(1).Clear
    With Sheets(1)
        .Range("c1", .Range("c" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(1).PasteSpecial xlPastValues
        .Range("g2", .Range("g" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(Rows.Count).End(xlUp)(2).PasteSpecial xlPastValues
    End With
    Sheets(2).Range("a1").CurrentRegion.Resize(, 1).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=.Cells(1, 1), unique:=True 
End Sub

Hi Jindon

It gaves me Compile Error: Variable no defined

p/s I thank to both of ya...

Regards

Vgabond

 

[erik.van.geit]

you didn't tell where the error occured, but fortunately it was easy to detect
"xlPastvalues" was a typo
quick fixed also some other things

try this

Sub copy_uniques()
'Erik Van Geit (modified by jindon) and again by Erik :-)
'070605
    Sheets(2).Columns(1).Clear
    With Sheets(1)
        .Range("c1", .Range("c" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(1).PasteSpecial xlPasteValues
        .Range("g2", .Range("g" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    End With
    With Sheets(2)
    .Range("a1").CurrentRegion.Resize(, 1).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=.Cells(1, 2), unique:=True
    .Columns(1).Delete
    End With
End Sub

 

[Vgabond]

jindon, as always, plenty of modesty

Vgabond, sorry, I didn't see the second "Agent" column . Must be professional deformation, because I would never use the same label for different columns.

I had no error: it worked for me.
Jindons solution will work to my sense: if you still have an error problem, please tell us on what codeline it arises.

Hi Eric..after I change it to code which modified by Jindon, it gaves me compile error :- Variable Not Defined ..and it highlighted xlPastValues in yellow

 

[Vgabond]

you didn't tell where the error occured, but fortunately it was easy to detect
"xlPastvalues" was a typo
quick fixed also some other things

try this

Sub copy_uniques()
'Erik Van Geit (modified by jindon) and again by Erik :-)
'070605
    Sheets(2).Columns(1).Clear
    With Sheets(1)
        .Range("c1", .Range("c" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(1).PasteSpecial xlPasteValues
        .Range("g2", .Range("g" & Rows.Count).End(xlUp)).Copy
        Sheets(2).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
    End With
    With Sheets(2)
    .Range("a1").CurrentRegion.Resize(, 1).AdvancedFilter _
    Action:=xlFilterCopy, CopyToRange:=.Cells(1, 2), unique:=True
    .Columns(1).Delete
    End With
End Sub

Eric ......It WORKS !!!!!!!

Thank you very much...........you too Jindon. Thanks a lot guys..You guys are the GREAT!

 

[erik.van.geit]

You're WELCOME!!

After some months of no MrExcel activity, I enjoyed to help a bit.

 

[Vgabond]

You're WELCOME!!

After some months of no MrExcel activity, I enjoyed to help a bit.

In that case...is it possible if you can add the run button to run the macro? TQ

 

[erik.van.geit]

was that a joke?
or did you ask for a walk through to create a button?

To create a button with the Forms Toolbar
Menu Display / Toolbars ==> choose Forms
click on the button-icon
drag a button on your sheet
(pressing Alt while doing this will align the button with one or some cells)
you will be asked to attach a macro to the button
(you can always set or change this rightclicking on the button)
choose the macro you want from the list

 

[Vgabond]

was that a joke?
or did you ask for a walk through to create a button?

To create a button with the Forms Toolbar
Menu Display / Toolbars ==> choose Forms
click on the button-icon
drag a button on your sheet
(pressing Alt while doing this will align the button with one or some cells)
you will be asked to attach a macro to the button
(you can always set or change this rightclicking on the button)
choose the macro you want from the list

Thanks Eric......Got it..