VBA code to remove duplicates

[Wafee]

Can someone help me with a code that does the below.

I have 3 source sheets of similar data from Sheet1, Sheet2, Sheet3 source sheets. I am combining the data in to a single sheet called "Sheet4". I am color coding all the data from sheet3 when pasted in sheet4. now once duplicates are identified from column "B" from sheet4 i am filtering the duplicates and sorting them (A-Z) and retaining the rows of data which has color coding(the data which is from sheet3) and removing rest from the filtered duplicates.

this what it looks likes once duplicates are filtered and sorted. data from 6- 10 is from sheet3 which is color coded. hence among the duplicates if there two lines of data with similar ref then color coded line must be replaced. please help me with suitable code (Text coloring is not necessary if there is any other way to do it.). also consider that below data is in filter mode.

Row number	Name	Ref	Status
1​	A​	337​	Submitted​
2​	A​	420​	Submitted​
3​	B​	554​	Submitted​
4​	B​	632​	Submitted​
5​	C​	343​	Submitted​
6​	C​	343​	Submitted​
7​	A​	337​	Submitted​
8​	A​	420​	Submitted​
9​	B​	632​	Submitted​
10​	B​	554​	Submitted​

 

[Larry Haydn]

Perhaps you can define what you need to achieve with these sets of data, rather than what you are doing now.

 

[Wafee]

Perhaps you can define what you need to achieve with these sets of data, rather than what you are doing now.

Hi Larry,

Whenever there are duplicates in terms of ref column("B" column) in sheet4 then I need to retain the line of data which came from sheet3 and remove others which might have come from either sheet1 or sheet2. Just to confirm again this task needs to be done for duplicates alone.