Previous Topic Next Topic

Macro for comparing 2 sheets in excel and removing duplicates

Closed
meetmani Posts 6 Registration date Monday May 5, 2014 Status Member Last seen May 8, 2014 - May 5, 2014 at 04:24 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 - May 12, 2014 at 10:57 AM
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...
Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
May 8, 2014 at 11:02 AM
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


meetmani Posts 6 Registration date Monday May 5, 2014 Status Member Last seen May 8, 2014
May 8, 2014 at 11:09 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
May 8, 2014 at 11:13 AM
But column Q of Sheet2 is already cleared when Sheet1 column P = Sheet2 column Q in the previous code??

What do you mean?
meetmani Posts 6 Registration date Monday May 5, 2014 Status Member Last seen May 8, 2014
May 8, 2014 at 11:40 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
May 12, 2014 at 10:57 AM
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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Contributor Last seen December 27, 2022 555
May 6, 2014 at 12:04 PM
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
meetmani Posts 6 Registration date Monday May 5, 2014 Status Member Last seen May 8, 2014
May 8, 2014 at 04:18 AM
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.