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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 12, 2014 at 10:57 AM
Related:
- How to compare two excel files for duplicates
- Excel mod apk for pc - Download - Spreadsheets
- How to open msi files on android - Guide
- Kernel for excel - Download - Backup and recovery
- Number to words in excel - Guide
- Clear recent files windows 11 - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 8, 2014 at 11:02 AM
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:
To make the first code work, try replacing Integer with Long in the second code line.
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 12, 2014 at 10:57 AM
May 12, 2014 at 10:57 AM
Hi Meetmani,
The following code will remove duplicates in sheet2 column Q:
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
May 6, 2014 at 12:04 PM
May 6, 2014 at 12:04 PM
Hi Meetmani,
Make sure your sheet names match those in the code below:
Best regards,
Trowa
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
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.
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.
May 8, 2014 at 11:09 AM
May 8, 2014 at 11:13 AM
What do you mean?
May 8, 2014 at 11:40 AM
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