LinkBack URL
About LinkBacks
I m looking for macro which finds duplicates quickly in huge excel sheet based on some key column/s and either put the data in another sheet or highlight in that sheet only.
The key column can be a single column or multiple columns.
I m looking for macro which finds duplicates quickly in huge excel sheet based on some key column/s and either put the data in another sheet or highlight in that sheet only.
The key column can be a single column or multiple columns.
Hello prachi b,
Something like the attached WorkBook perhaps?
Also see the "Please consider" note at the bottom of this post.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Thanx Winon for a quick response.
But this is not according to my requirement.
Consider the following entries in an excel sheet below -
ID Amt Remarks Month
---- -------- ------------------- -----------
1 200.00 Balance November
2 456.59 Paid November
3 344.08 Balance November
1 157.02 Balance November
2 456.59 Paid November
Case 1:
Consider ID as key column. For ID "1", there are two records which are considered as duplicates.
ID Amt Remarks Month
---- -------- ------------------- -----------
1 200.00 Balance November
1 157.02 Balance November
Case 2:
Considering ID column and Amt column as key column,now there are two duplicates records as follows:
ID Amt Remarks Month
---- -------- ------------------- -----------
2 456.59 Paid November
2 456.59 Paid November
Likewise there can be any combination of key columns to identify duplicates in a sheet.
Is there a single which can handle these cases.
Please let me know if you have any questions.
Thanks in advance.
Hello prachi b,
I don't believe you can use a single Macro to identify your key columns. You will have to tell the Program where to look for duplicates, and what you specify as a "Key".Likewise there can be any combination of key columns to identify duplicates in a sheet.
Is there a single which can handle these cases.
The revised Sample WorkBook should give you a fair indication of how it works, and you can expand from thereon.
Hey Winon,
Sub ShowDups()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).Cells.Resize(, 3).Interior.ColorIndex = 8
End If
Next x
End Sub
In the above code where you are defining key column....pls let me know...so that i can change the key column whenever requirred.
And also pls guide how we can pass key column to macro...
sry for troubling...
i m a new in macro world....
If you just want to identify the duplicates then why don't use just use Conditional Formatting >> Highlight Cell Rules >> Duplicate values?
Please consider:
Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks