+ Reply to Thread
Results 1 to 18 of 18

delete 'all' double data.

  1. #1
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    delete 'all' double data.

    Hi!

    Can anyone help me with something.
    I want to delete all double data in excel but I don't know how. Can anyone help me?

    amsterdam
    new york
    new york
    London
    berlin
    paris
    paris
    Sidney

    has to become without new york and paris:

    amsterdam
    London
    berlin
    Sidney

    I'm using excel 16.5.

    Thank you!!!
    Adriaan.
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, Suffolk (England), but grew up in Sawley, Derbyshire
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2211 (Windows 11 22H2 64-bit)
    Posts
    65,402

    Re: delete 'all' double data.

    If you want the list as shown in the screenshot, with gaps, then it's easy, but I suspect that's not really what you want. Can you confirm?

    By the way, Sydney is misspelt.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B 2016
    Posts
    1,940

    Re: delete 'all' double data.

    Formula for C1 =IF(COUNTIF($A$1:$A$8,A1)=1,A1,"")

  4. #4
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    OH yes, I saw something was wrong... Sydney... sorry . But thanks for your quick reply AliGW

    Oh I see it's actually more complicated than I wrote.
    In the attached file you see what I have. You see double ones and I want to delete 'all' of the double ones.

    I need it in a new tab.

    Do you know how?
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,973

    Re: delete 'all' double data.

    A2:
    =IFERROR(INDEX(Blad1!$A$2:$A$23, MATCH(0, INDEX( COUNTIF( A$1:A1, Blad1!$A$2:$A$23)+ (COUNTIF( Blad1!$A$2:$A$23, Blad1!$A$2:$A$23)<>1),), 0)),"")

    copied down. Then a simple VLOOKUP for B2, copied across and down.

    =IFERROR(VLOOKUP($A2,Blad1!$A:$AA,1+COLUMNS($B2:B2),FALSE)&"","")
    Attached Files Attached Files
    Glenn



  6. #6
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: delete 'all' double data.

    Select A1 goto tab data-->remove dublicates

    MS-Off Ver 16.50?
    Messages have been translated from Dutch to English by means of google translate.

  7. #7
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    A2:
    =IFERROR(INDEX(Blad1!$A$2:$A$23, MATCH(0, INDEX( COUNTIF( A$1:A1, Blad1!$A$2:$A$23)+ (COUNTIF( Blad1!$A$2:$A$23, Blad1!$A$2:$A$23)<>1),), 0)),"")

    copied down. Then a simple VLOOKUP for B2, copied across and down.

    =IFERROR(VLOOKUP($A2,Blad1!$A:$AA,1+COLUMNS($B2:B2),FALSE)&"","")


    The results looks good Glenn!

    The only thing is that I'm new to these codes and I don't know how to take the right steps to implement this.
    Last edited by adriaanvandam; 06-27-2021 at 04:10 PM.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,973

    Re: delete 'all' double data.

    I'm not sure what you mean... Was it not clear from the attached file? If you have problems, ask. If not...

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,048

    Re: delete 'all' double data.

    I believe V16.50 is a MAC version and is part of MS365, correct? If so, you can just use this formula in cell A2 on another sheet - no need to copy down as it will spill:

    =UNIQUE(Blad1!A2:AA23)

    (Adjust the array to whatever columns you want to see, i.e., if you just want to see the id and first name, you would enter:


    =UNIQUE(Blad1!A2:B23)

  10. #10
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    Thanks!
    I have to repeat this task with other files so I have to understand how to do this.
    I tried, tried, and tried to find out how. I'm missing the basic knowledge of excel to take the right steps

    If you can give simple instructions then it would be helpful.

    Sorry for this.

    I'm not using 365 but a retail licence.
    Last edited by adriaanvandam; 06-28-2021 at 10:47 AM.

  11. #11
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: delete 'all' double data.

    I would just remove the picture. It is not very wise to share this on a public forum.

  12. #12
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,048

    Re: delete 'all' double data.

    @adriaanvandam - I assume you are referring to my post (#9)? If so, if you don't have MS365 with the dynamic array formulas available to you, then there's no sense in explaining how they work. If you are addressing someone else, please put address them accordingly.

  13. #13
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    Gregb11.. I'm not using MS365. but another one.
    And sorry I was mentioned to the post of Glenn Kennedy when writing.

    I have to repeat this task with other files so I have to understand how to do this.
    I tried, tried, and tried to find out how. I'm missing the basic knowledge of excel to take the right steps

    If you (glennkennedy) can give simple instructions then it would be helpful.

    Sorry for this and thanks until know .

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,973

    Re: delete 'all' double data.

    I think I really need to see what you have done to try to implement the formula...

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,973

    Re: delete 'all' double data.

    I suspect that you did NOT open the file in Post 5. BOTH formulae are in place and working in that file on sheet 1. It transfers the ciorrect raw data FROM Blad 1. The formulae DO NOT go on Blad 1...

  16. #16
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    Glenn, I used your file at post 5, and I see that it works. Now I want to use it on other files with other data. But I don't know to do this. I send you a privite message and email.
    But I don't want to bother you with my lack of knowledge.

  17. #17
    Registered User
    Join Date
    06-27-2021
    Location
    Helvoirt
    MS-Off Ver
    16.50
    Posts
    8

    Re: delete 'all' double data.

    Hi everyone, thanks for your help. It solved. I asked someone to make a file with a macro who does the job automatically with a button .

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,973

    Re: delete 'all' double data.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] double post delete
    By almostgenius in forum Excel General
    Replies: 2
    Last Post: 08-25-2015, 04:16 PM
  2. Double click cell - copy data to new sheet - create a list from double clicking
    By kakky in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-04-2015, 11:14 PM
  3. Match and delete row with double click
    By johnny_p in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-14-2013, 05:12 PM
  4. Delete double rows IF...
    By Turbobiker in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-29-2009, 08:12 AM
  5. How do I delete multiple double entries?
    By wandsworth in forum Excel General
    Replies: 2
    Last Post: 12-08-2007, 03:31 PM
  6. how to filter data and delete double records
    By **dan** in forum Excel General
    Replies: 2
    Last Post: 10-17-2006, 06:07 AM
  7. [SOLVED] Delete row if double
    By bahri in forum Excel General
    Replies: 2
    Last Post: 08-05-2006, 11:25 AM

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