Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Macro for comparing 2 sheets in excel and removing duplicates [Closed]

Report
Posts
6
Registration date
Monday May 5, 2014
Status
Member
Last seen
May 8, 2014
 -
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 -
Hi,

Kindly help me out with Macro for " Comparing 2 sheets in excel and removing duplicates in sheet 2 by replacing duplicates with blanks" so order of the excel sheet will not change.

In Sheet 1 I have data from column A-D
In sheet 2 I have data from column A-E

I want to compare column B in both sheets and need to replace duplicates entries in Sheet 2 Column B with blanks.

Kindly help me on this...
See more
Related:

3 replies

Answer 1 / 3
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Meetmani,

So you want to compare Sheet1 column P with Sheet2 column Q.
When they are the same, remove contents from Sheet2 column P and Q.

If this is true then the following code will work: 
Sub RunMe()
Dim lRow As Long

Sheets("Sheet1").Select
lRow = Range("P" & Rows.Count).End(xlUp).Row

For Each cell In Range("P2:P" & lRow) 'Assuming you have a 1 row header
    If cell.Value = Sheets("Sheet2").Cells(cell.Row, "Q") Then
        Sheets("Sheet2").Range(Cells(cell.Row, "P"), Cells(cell.Row, "Q")).ClearContents
    End If
Next cell

End Sub


To make the first code work, try replacing Integer with Long in the second code line.

Best regards,
Trowa


Report
Posts
6
Registration date
Monday May 5, 2014
Status
Member
Last seen
May 8, 2014
Thanks.. After Comparing the sheets.. I want to remove duplicates and replace with blanks(so order of the excel sheet will not change) from Column Q in Sheet2. Pls provide macro for that also
Report
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
But column Q of Sheet2 is already cleared when Sheet1 column P = Sheet2 column Q in the previous code??

What do you mean?
Report
Posts
6
Registration date
Monday May 5, 2014
Status
Member
Last seen
May 8, 2014
i want to check any duplicates are there in sheet2 column Q. See my below example of wat am looking

Sheet1-Master sheet

ColumnP
12
14
15
17
18
19
20

Sheet2

ColumnQ
12
13
1
2
8
9
9
8
7
7
20
19

Our macro will remove 12,20,19 respectively. But in sheet2 we can see duplicates(9,8,7) still there. I want to remove those duplicates also and replace them by blanks
Answer 2 / 3
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Meetmani,

The following code will remove duplicates in sheet2 column Q: 
Sub RunMe()
Dim lRow As Long

Sheets("Sheet2").Select
lRow = Range("Q1").End(xlDown).Row

For Each cell In Range("Q2:Q" & lRow)
    x = 1
    
    Do
        
        If cell.Value = Cells(cell.Row + x, "Q").Value Then
            Cells(cell.Row + x, "Q").ClearContents
        End If
        
        x = x + 1
        
    Loop Until x = lRow + 1
    
Next cell
        
End Sub


Best regards,
Trowa

Answer 3 / 3
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
Hi Meetmani,

Make sure your sheet names match those in the code below: 
Sub RunMe()
Dim lRow As Integer

Sheets("Sheet1").Select
lRow = Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("B2:B" & lRow) 'Assuming you have a 1 row header
    If cell.Value = Sheets("Sheet2").Cells(cell.Row, "B") Then
        Sheets("Sheet2").Cells(cell.Row, "B").ClearContents
    End If
Next cell

End Sub


Best regards,
Trowa
Report
Posts
6
Registration date
Monday May 5, 2014
Status
Member
Last seen
May 8, 2014
Hi Trowa,

Thanks for your reply. its working for data with few rows only when I run this macro in huge data its not working.

It will be greatful if you provide macro based on below criteria

I was having huge amount of data in "sheet2" from columns A to R, in the data "Column Q" is unique value

I was having huge amount of data(Master Data base) in "Sheet1" from columns A to Q, in the data "Column P" is unique

Now I want a macro to compare both sheets (Sheet1 and Sheet2) and remove duplicate entries in column Q in Sheet2 by comparing with sheet1 in column P and replace duplicates by blanks. Also need to remove duplicates and replace with blanks in Sheet2 in column P.

Its my requirement kindly help me on this.
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!