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.
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.
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
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
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
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
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.
- Posts
- 6
- Registration date
- Monday May 5, 2014
- Status
- Member
- Last seen
- May 8, 2014
-- Posts
- 2570
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- December 3, 2019
379 -What do you mean?
- Posts
- 6
- Registration date
- Monday May 5, 2014
- Status
- Member
- Last seen
- May 8, 2014
-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