Removing Duplicates based on some criteria

[Wafee]

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 - Name	Column B - ID's	Column F - Source name
John	324	S2
Mary	566	S1
Mary	566	S2
Bane	456	S1
John	324	S3
Bane	456	S3

 

[abdulads]

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

 

[abdulads]

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

 

[Wafee]

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.

 

[Wafee]

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.

 

[abdulads]

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.

 

[Wafee]

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.