Compare two columns and delete duplicates
Solved/Closed
Related:
- Compare two columns and delete duplicates
- Delete my whatsapp account without app - Guide
- Beyond compare - Download - File management
- Display two columns in data validation list but return only one - Guide
- How to delete whatsapp account without phone - Guide
- How to delete snapchat account - Guide
1 response
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Mar 16, 2010 at 09:16 PM
Mar 16, 2010 at 09:16 PM
This is a hybrid soltuion-sheet solution and macro
Your are messing the data . so KEEP THE DATA SAFELY SOME WHERE FOR EASY RETRIEVAL.Iin this sample workbook you copy the data in another sheet e.g. sheet 3
row 1 is having some headings
the sample data is like this:
hdng1 hdng2 hdng3 hdng4
00001 AAAA1 xxxx yyyy
00001 BBBB1 xxxx xxxx
00001 BBBB2 yyyy yyyy
00002 AAAA1 yyyy xxxx
00002 AAAA1 yyyy xxxx
00003 AAAA1 xxxx yyyy
In E2 type this formula(E1 is having some heading)
=A2&B2
copy E2 down. easy way of copying down large no of rows is take the cursor to the right bottom of the cell E2. The cursor turns into sign +. click this + sign. E2 will be copied down the rows in column E
now run this macro given below.
once the macro is run the sixth row will be deleted. to test for a rerun of the macro delete data in sheet 1 and copy it in from sheet3 and again run the macro
In the original file also you can copy the data in sheet 1 in sheet 3 also in addition to SAVING THE DATA SOMEWHERE ELSE SAFELY.
THE MACRO IS
Finally after running the macro and your job is over you can delete the column E if necessary.
Your are messing the data . so KEEP THE DATA SAFELY SOME WHERE FOR EASY RETRIEVAL.Iin this sample workbook you copy the data in another sheet e.g. sheet 3
row 1 is having some headings
the sample data is like this:
hdng1 hdng2 hdng3 hdng4
00001 AAAA1 xxxx yyyy
00001 BBBB1 xxxx xxxx
00001 BBBB2 yyyy yyyy
00002 AAAA1 yyyy xxxx
00002 AAAA1 yyyy xxxx
00003 AAAA1 xxxx yyyy
In E2 type this formula(E1 is having some heading)
=A2&B2
copy E2 down. easy way of copying down large no of rows is take the cursor to the right bottom of the cell E2. The cursor turns into sign +. click this + sign. E2 will be copied down the rows in column E
now run this macro given below.
once the macro is run the sixth row will be deleted. to test for a rerun of the macro delete data in sheet 1 and copy it in from sheet3 and again run the macro
In the original file also you can copy the data in sheet 1 in sheet 3 also in addition to SAVING THE DATA SOMEWHERE ELSE SAFELY.
THE MACRO IS
Sub text()
Dim j As Integer, k As Integer, r As Range
j = Range("E2").End(xlDown).Row
For k = j To 2 Step -1
MsgBox k
Set r = Range(Cells(k, "E"), Cells(k, "E").End(xlUp))
If WorksheetFunction.CountIf(r, Cells(k, "E")) > 1 Then
Cells(k, "E").EntireRow.Delete
End If
Next k
End Sub
Finally after running the macro and your job is over you can delete the column E if necessary.
Mar 17, 2010 at 11:07 AM