Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate records
I am combining contact records from 2 different xls worksheets and need to
delete the duplicates. I need to know how to - 1. identify the duplicates [Assuming column A contains last name and column B first name, I have done this through =IF((A2=A1)*(B2=B1), "duplicate", "") but dont know if there is a better way :-)] 2. tranfer any missing information from duplicate to the reference record [additional information in columns C-Z] 3. Delete the duplicate record Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate records
Check out the advanced filter - DataFilterAdvanced Filter. There is
the option to copy unique records to another range. Cliff Edwards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting duplicate records
you can use COUNTIF Function for example =countif(B:B,B1)
OR you will also use this macros Sub FindRepeatedValues() ac = Application.Calculation Application.Calculation = xlCalculationManual mb = MsgBox("Finding repeated values - Press Yes to highlight, No to delete", vbYesNoCancel) If mb = 2 Then Exit Sub r = Selection.Row c = Selection.Column rr = Selection.Rows.Count - 1 cc = Selection.Columns.Count - 1 i = -1 Do While rr 0 i = i + 1 If Cells(r + i, c).Value = Cells(r + i + 1, c).Value Then If mb = 6 Then With Range(Cells(r + i + 1, c), Cells(r + i + 1, c + cc)).Interior .ColorIndex = 36 .Pattern = xlSolid End With Else Range(Cells(r + i + 1, c), Cells(r + i + 1, c + cc)).Delete shift:=xlUp i = i - 1 End If End If rr = rr - 1 Loop Application.Calculation = ac End Sub Hope this will help u cheers Hardeep kanwar "ward376" wrote: Check out the advanced filter - DataFilterAdvanced Filter. There is the option to copy unique records to another range. Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Duplicate Records across multiple sheets | Excel Worksheet Functions | |||
How do I display duplicate records in Excel without deleting? | Excel Worksheet Functions | |||
Deleting duplicate records | Excel Discussion (Misc queries) | |||
Deleting both duplicate records | Excel Discussion (Misc queries) | |||
deleting duplicate records in a mail merge | Excel Discussion (Misc queries) |