Search Duplicate and Result in Next Worksheet

Vgabond

Vgabond

Board Regular
Joined
Jul 22, 2008
Messages
197
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
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
I assume this is your input sheet? What would you want your output sheet to look like, as I don't understand from your words what you are wanting.
 
Upvote 0
sijpie said:
I assume this is your input sheet? What would you want your output sheet to look like, as I don't understand from your words what you are wanting.
Click to expand...

Hi There sijpie

The output worksheet should be only contain the agent name without any more duplication.

Regards

Azman
 
Upvote 0
Hi, try this
Code: 
Option Explicit
 
Sub copy_uniques()
'Erik Van Geit
'070605
Dim rng As Range
Dim LR As Long              'Last Row
 
    With Sheets(1)
    LR = .Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = .Range(.Cells(1, 3), .Cells(LR, 3))
    End With
    Application.ScreenUpdating = False
    
    With Sheets(2)
    .Columns(1).ClearContents
    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, 1), unique:=True
    End With
    Application.ScreenUpdating = True
 
End Sub
The list of agents on sheet 1 in column C (3) will go to sheet 2 column A.

Code: 
  A      
1 Agent  
2 LPG121 
3 LPG14  
4 LPG142 
5 LPG173 
6 LPG16  
7 LPG6   
8 LPGA4  
ResultsSheet
[Table-It] version 09 by Erik Van Geit

Your example didn't contain duplicates as far as I can see!?

kind regards,
Erik
 
Upvote 0
erik.van.geit said:
Hi, try this
Code: 
Option Explicit
 
Sub copy_uniques()
'Erik Van Geit
'070605
Dim rng As Range
Dim LR As Long              'Last Row
 
    With Sheets(1)
    LR = .Cells(Rows.Count, 3).End(xlUp).Row
    Set rng = .Range(.Cells(1, 3), .Cells(LR, 3))
    End With
    Application.ScreenUpdating = False
 
    With Sheets(2)
    .Columns(1).ClearContents
    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Cells(1, 1), unique:=True
    End With
    Application.ScreenUpdating = True
 
End Sub
The list of agents on sheet 1 in column C (3) will go to sheet 2 column A.

Code: 
  A      
1 Agent  
2 LPG121 
3 LPG14  
4 LPG142 
5 LPG173 
6 LPG16  
7 LPG6   
8 LPGA4  
ResultsSheet
[Table-It] version 09 by Erik Van Geit

Your example didn't contain duplicates as far as I can see!?

kind regards,
Erik
Click to expand...

Hi Eric

Thanks but there's an error :- Run Time Error 9, subscript out of range. Anyway I've duplicated list which is LPG142 , LPG121.
 
Upvote 0
You should thank to Erik
try
Code: 
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
 
Upvote 0
jindon, as always, plenty of modesty :)

Vgabond, sorry, I didn't see the second "Agent" column :cool:. Must be professional deformation, because I would never use the same label for different columns.
Run Time Error 9, subscript out of range
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Nested IF Statement
Replies
5
Views
253
Fluff
Fluff
F
  • Question
Merging/combining dup data in rows and Summing data in columns
Replies
6
Views
235
Fuji
F
O
  • Question
Highlighting different sets of duplicates with different colours/ symbols; but it has to be more than 3
Replies
1
Views
322
Orkochu
O
C
  • Solved
Unique sequential values
Replies
3
Views
224
awoohaw
awoohaw
D
  • Question
Results by handicap - with ties. Look up by score without duplicates
Replies
4
Views
354
dkjonesau
D

Forum statistics

Threads
1,221,582
Messages
6,160,633
Members
451,661
Latest member
hamdan17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top