+ Reply to Thread
Results 1 to 14 of 14

delete duplicate

  1. #1
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Angry delete duplicate

    I want to delete duplicate data with match the both data

    for an example

    1.1778, LORONG F4 OFF JALAN STUTONG TAMAN SATRIA JAYA BDC, KUCHING SARAWAK
    2. NO 1778, LORONG F4, JALAN STUTONG, TAMAN SATRIA JAYA BDC,

    i want to delete the first one and remain same the second one..

    Can anyone suggest or help me in this ..
    I have 5000 data's

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: delete duplicate

    Hi Mathanraj,

    Welcome to the forum.

    How are you saying that first one should be deleted... I see that second one is fully covered in first one, so second one should be deleted. what u say ?

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    Okay..thanks
    I have attached the files .See the column "Mailing Address Line 1","Mailing Address Line 2","Mailing Address Line 3","Mailing Address Line 4"

    some data are duplicate at same but some have duplicated but first data have coma but the second don't have coma.. or vise versa
    So I really don't know how to do .. in ms excel to extract those data out
    I really appreciated your help and thanks for me help me..

    M.really new in programming but like to learn all this..
    Attached Files Attached Files
    Last edited by jeffreybrown; 12-30-2012 at 12:23 PM. Reason: Please do not quote entire quotes. It's unnecessary and it causes clutter.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: delete duplicate

    hmm.. quite confusing.
    My suggestions:-
    1) For account number 12480007592951, there are two addresses and one need to be removed.. correct? If yes, then I would suggest you to use Len function i.e., write below formula in F1 and drag till F2.. you'll notice that F1 as 72 and F2 as 58. and now you can remove 58 result row because that row is already covered in 72 result's row being big enough.

    2) There are some customers like MOHD SAIFUL BIN SABJAN, having different address but same Zip code.. what you'll do in this case ? I would suggest you to keep first one ( which is having all four address column filled in as that appears to be complete and correct one .. again you can use LEN function in these type of cases.

    As per my observation there is no single logic which can be followed in this type of data ..

    Regards,
    DILIPandey
    <click on below * if this helps>

  5. #5
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    Ok..Thanks

    All the customer may have more then one customer ..But i need delete either one ...
    But Can u show me so samples on this..
    Because really can't understand..

    M..really thanks on you ...
    Last edited by jeffreybrown; 12-30-2012 at 12:23 PM. Reason: Please do not quote entire quotes. It's unnecessary and it causes clutter.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: delete duplicate

    mathanraj, pleased do not keep "reply with quotes" when you reply, it just takes up unnecessary room
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: delete duplicate

    Hi Mathanraj,

    Its your data and you are the best judge and best person to comment which rows to retain and which rows to delete.. I have just given my observations. If you want to learn about LEN function, it is just a function which returns the length of the characters in a cell.. for example if a1 has "DILIPandey", the formula = len(a1) will return 10. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    Is that any other method which u know better where u recommend me ..
    This project should be process on january 2013 ...
    If possible can u teach me the method which u think the best..

    I want to delete the duplicate address...

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: delete duplicate

    Hi Mathanraj76,

    I understand your situation.. To delete the duplicate, first we need to understand which one is "duplicate" and you are best person to judge that.. for example if a zip code is having two address and both are different, then how will you identify which one is duplicate .. and things like that.

    Once you identified the duplicate, then we can surely delete easily them and I guess your actual question is "how to identify duplicate" which unfortunately, only you can answer .. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    If like tht can the excel program check column address "F1" and "G1" are almost same it should delete either one
    Thanks

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: delete duplicate

    See if this works for you
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    Thanks
    But this program deleting column "F","G" and "H" which have address of customer..
    I want to delete the name and address of duplicate which almost same and those the name of name in column"D" are same with or join with name in column "C" are same then delete if not then don't delete..

    I have attached the data file with this mail..
    Thanks
    Raj
    Attached Files Attached Files

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: delete duplicate

    No idea about what you are talking about.

    You will get better solution(s), if you upload a file with your desired result.

    I think I can't help you more.

  14. #14
    Forum Contributor
    Join Date
    10-02-2012
    Location
    malaysia
    MS-Off Ver
    Excel 2016
    Posts
    140

    Re: delete duplicate

    Wht i meant ..Those name in column "C" have duplicate as well with column "F,G,H,I"
    If the customer name in column "C" are same and the address r almost same in "F,G.H.I" then delete off...
    Currently I'm Doing Manually ..The Column "D" name are same with column "C" then delete off..
    Pls help me ...

    I already attached the files ..for you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1