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 Moderator 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 Moderator 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


0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator 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?
0
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
0
Answer 2 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator 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

0
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator 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
-1
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.
0