Removing Duplicates based on some criteria

Wafee

Board Regular
Joined
May 27, 2020
Messages
104
Office Version
  1. 2013
Platform
  1. Windows
I have sheet with some data where B column has some ID's out of which few are duplicates. This data is from 3 sources which are S1, S2, S3. data source is present in "F" column.
Can someone help me with a VBA code that finds the duplicates from column "B" and then out of the dupicates find if one line of data is from S1 or S2 and other line is from S3 then the code has to retain line from S3 and need to delete lines from S1 and S2.

Below is the sample data. The code has to remove 1st and 4th row data.

Column A - NameColumn B - ID'sColumn F - Source name
John324S2
Mary566S1
Mary566S2
Bane456S1
John324S3
Bane456S3
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Please use remove duplicate in the Excel..

Select the table you want to remove duplicate .. Press ALT + A and then M

Select the check boxes you wanted the the criteria .. Here you will select Name and ID;s and click ok and they are gone
 
Upvote 0
For VBA you can use the below code for the 7 records.

Sub Macro15()
'
' Macro15 Macro
'

'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

ActiveSheet.Range("$A$1:$C$7").RemoveDuplicates Columns:=Array(1, 2), Header _
:=xlYes
End Sub
 
Upvote 0
Please use remove duplicate in the Excel..

Select the table you want to remove duplicate .. Press ALT + A and then M

Select the check boxes you wanted the the criteria .. Here you will select Name and ID;s and click ok and they are gone
Hi mate,

It is a part of automation I which i am working on so must need a code to do it rather than doing manually. Thanks for the help.
 
Upvote 0
For VBA you can use the below code for the 7 records.

Sub Macro15()
'
' Macro15 Macro
'

'
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

ActiveSheet.Range("$A$1:$C$7").RemoveDuplicates Columns:=Array(1, 2), Header _
:=xlYes
End Sub

Thanks mate.
But I don't see the the "F" column criteria in the above code. Can you able to help me with that.
 
Upvote 0
Since what we understand the criteria you looked for Column A and B we included two if you include column F which is not identical so you will not have any duplicate then. since in first row in F column is S2 and in Fourth is S3 nothing will be removed.
Thanks mate.
But I don't see the the "F" column criteria in the above code. Can you able to help me with that.
 
Upvote 0
Since what we understand the criteria you looked for Column A and B we included two if you include column F which is not identical so you will not have any duplicate then. since in first row in F column is S2 and in Fourth is S3 nothing will be removed.
Duplicates needs to be found from Column B alone once they are found from the duplicates if we have data from S1 or S2 in one line and S3 in other (referring to F column) then the code has to remove line which has S1 or S2 and retain the lines which has S3 in F column.
 
Upvote 0

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

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