none
Help with Excel sheets comparision, merge and eliminate duplicates of my contacts lists RRS feed

  • Question

  • Hi Folks

    I have about 4 excel sheets all with contacts exported from my Iphone ( 5171 contacts), People.live.com (3483), Nokia Lumia Phone (6037 contacts) and ATT address book ( 161 contacts). They all are definitely duplicates

    Each of them when exported have different headers names. If I try to do vlookup by first name or last name i will still have conflicts. I thought of doing by mobile numbers but that too is not possible as I have multiple column headers for mobile phones itself and they are stored in different columns.

    I uploaded all these excel sheets into my google email id and applied the duplicate elimination but it resulted in about 11018 contacts which I think is incorrect.

    My requirement is to merge all these contacts, remove duplicates and make a single contacts sheet before I upload into my new windows phone.  Can you please suggest some ideas for me ? or are there any freeware to resolve this issue ?

    Tuesday, June 9, 2015 9:24 PM

Answers

  • Hi,

    >> My requirement is to merge all these contacts, remove duplicates and make a single contacts sheet before I upload into my new windows phone

    It seems that you are going to merge all the worksheets into one without duplicating rows. I have made a demo, it will merge four sheets into a new worksheet.(all the sheets have the same column name). You can download the file here, you may adapt the demo to satisfy your requirement.

    Demo : http://1drv.ms/1I0OwPB

    Sub CreateContacts() Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Contacts" For i = 1 To 4 Call CreateSheets(Sheets(i), Worksheets("Contacts")) Next i MsgBox "Finished" End Sub 'Compare two Rows Function CompareRows(Row1 As Range, Row2 As Range) For i = 1 To 2 (demo row have two columns) If Row1.Columns(i).Value = Row2.Columns(i).Value Then CompareRows = True Else CompareRows = False

    Exit For End If Next i End Function 'Compare worksheets Function CreateSheets(sheet1 As Worksheet, Sheet2 As Worksheet) Rowcount1 = sheet1.UsedRange.Rows.Count Rowcount2 = Sheet2.UsedRange.Rows.Count For i = 1 To Rowcount1 For j = 1 To Rowcount2 flag = CompareRows(sheet1.Rows(i), Sheet2.Rows(j)) m = 20 If flag = True Then Exit For End If Next j If flag = False Then Sheet2.Rows(Rowcount2).Value = sheet1.Rows(i).Value Rowcount2 = Rowcount2 + 1 End If Next i Sheet2.Cells(ColumnIndex, 1).Value = " " End Function

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Wednesday, June 10, 2015 9:09 AM
    Moderator

All replies

  • Hello:

    I wish there was a simple way to accomplish your goal, but usually it involves some list comparison software and then ultimately you have to manually look at the exceptions and make the final decision.

    I did a project similar to your requirements, and you have two alternatives:

    (1) There are software list compare packages out there (usually they cost money and you have to upload your data)

    (2) You can write your own system

    One approach is to merge all the data, making sure there is still a code that identifies the source (i.e. from ATT, Nokia, etc).  Then sort all the data by last name and first name.  With VBA, you can identify exact matches and pull them onto a clean worksheet.  Then, when it comes to the issues, such as "Jones, Jerry" vs. Jones, Jeremy", you can select the last names that match and then color code the first names that are close but not an exact match.  The ones with no match at all can also be moved to another clean worksheet.

    No matter how you approach the issue, there is still the necessity to manually intervene.

    If you know VBA, then you could use a VLookup or Match command to do all the comparisons.

    Wish I had better news for you.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, June 9, 2015 11:44 PM
  • Hi,

    >> My requirement is to merge all these contacts, remove duplicates and make a single contacts sheet before I upload into my new windows phone

    It seems that you are going to merge all the worksheets into one without duplicating rows. I have made a demo, it will merge four sheets into a new worksheet.(all the sheets have the same column name). You can download the file here, you may adapt the demo to satisfy your requirement.

    Demo : http://1drv.ms/1I0OwPB

    Sub CreateContacts() Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Contacts" For i = 1 To 4 Call CreateSheets(Sheets(i), Worksheets("Contacts")) Next i MsgBox "Finished" End Sub 'Compare two Rows Function CompareRows(Row1 As Range, Row2 As Range) For i = 1 To 2 (demo row have two columns) If Row1.Columns(i).Value = Row2.Columns(i).Value Then CompareRows = True Else CompareRows = False

    Exit For End If Next i End Function 'Compare worksheets Function CreateSheets(sheet1 As Worksheet, Sheet2 As Worksheet) Rowcount1 = sheet1.UsedRange.Rows.Count Rowcount2 = Sheet2.UsedRange.Rows.Count For i = 1 To Rowcount1 For j = 1 To Rowcount2 flag = CompareRows(sheet1.Rows(i), Sheet2.Rows(j)) m = 20 If flag = True Then Exit For End If Next j If flag = False Then Sheet2.Rows(Rowcount2).Value = sheet1.Rows(i).Value Rowcount2 = Rowcount2 + 1 End If Next i Sheet2.Cells(ColumnIndex, 1).Value = " " End Function

    Hope this could help you

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.




    Wednesday, June 10, 2015 9:09 AM
    Moderator