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.
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Formula for C1 =IF(COUNTIF($A$1:$A$8,A1)=1,A1,"")
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?
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)&"","")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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.
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.
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.
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)
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.
I would just remove the picture. It is not very wise to share this on a public forum.
@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.
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.
I think I really need to see what you have done to try to implement the formula...
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...
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.
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
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks