Thank you Mr. John With Your Help ill Finish My Work Just 15 Min And am gonna play with my Daughter Thanks a Lot
@GE-Excel
2 жыл бұрын
Though there are many ways to do it, Youur method of carrying the whole thing was good. Goodluck
@suraksha7841
4 жыл бұрын
Thank you for this great video. Just forgot how to remove duplicates, but this video of yours is very helpful. Keep the amazing work.
@playinJ30
5 жыл бұрын
There is a way to do this with formulas as well that took me a while to develop, but it's quite simple... you can use a countif statement to count the total number of an entry. You remove the cell lock from the data range in the formula. What this does is say we have John Smith listed 3 times... the first entry will show 3, 2nd will return 2 and last will return 1. Create an if statement around the countif statement to return a blank ("") if the countif statement returns more than 1. This puts a 1 next to just one of the lines of the duplicates... from there you can write another column that uses an if statement that creates a numbered list for reference on a vlookup... you do this by if the countif formula column shows a 1, you tell it to add a number to the row above... if it shows a blank, it will return the number above. So for all 3 john smith rows, it will show 1, 1, 1 in this column. If Jane Doe is next with 2 entries... it will show 2, 2. Vlookups always look for the first reference in the array so if you just create a numbered list and vlookup on the number from the last formula column I described... 1. John Smith, 2. Jane Doe, etc etc. You could also create a 3rd column to mirror the first formula column to blank out the additional 1s and 2s to keep it cleaner or for another function that needs it. If you want to do this based on multiple columns data to return unique combinations of data, just concatenate the columns you want then do the above for that column instead of the Name column. The beauty of this is that it doesn't require a macro (buggy and I suck at VBA.. but certainly it will work and this video explains it well.. kudos) and then it allows you to just copy and paste raw data into an range and it automatically creates the list you want with formulas rather than having to copy and paste a column, run remove duplicates, etc. This is super useful for things like running payroll calcs when you have a list of time entries from employees. Also, all this may be inconsequential now because Excel and Google sheets have a UNIQUE function which does all this for you, ha
@bhekuzindenobunga7012
4 жыл бұрын
Your videos are absolutely helpful. Thanks for sharing !
@barttitulaerexcelbart9400
6 жыл бұрын
Thank you John. You could also use advanced filter, you first have to copy the header of the column you want to "unduplicate". If you always update your original table and always use the same column, you coud also consider power query to do this.
@ExcelCampus
6 жыл бұрын
Great suggestions Bart! I will do a follow-up video on Advanced Filter. I always forget about that technique, but it's another good one. Thank you! :-)
@andreycuellar1160
Жыл бұрын
Thank you! You're an actual lifesaver!
@Ahmed-Afkir
2 жыл бұрын
Thanks for your clear explantion Mr Jon
@mrCetus
2 жыл бұрын
Hi Jon, I want to find duplicates from 2 lists and paste the found duplicates in another sheet. How can I do that?
@AngelaLuvPink
4 жыл бұрын
THANKS , IT WORKS. have a great day
@That_AboElHamd
4 жыл бұрын
Thank you so much. I was actually struggling with a work report and this makes it so much easier.
@husam_kirkukhusam9840
3 жыл бұрын
Xaz
@dillonbarclay8373
5 жыл бұрын
Really well done, nice walk through on the macro.
@pawjeppesen-ifttttricks605
4 жыл бұрын
The macro is exactly what I need!! One question. What if I want to count how many times a person is represented in the original dataset? Can I add a formula to the macro? //Paw
@exclusivelynyc
3 жыл бұрын
Great video. Thank you for sharing. How do I do exactly this but instead of copying it to a new spread sheet, I like to copy it to a master list of people in column A, sheet 1. Thanks. I have list I get every day, some are new names. Just want to add the new names and remove the duplicates.
@dalia20006
6 жыл бұрын
This was really useful. Thank you Jon
@WarbirdPylonRacer
4 жыл бұрын
Worked great! Thank you for making this video so I didn't need to read and search out how to do it in the cumbersome Excel help window. ;-)
@danac9656
6 жыл бұрын
best excel instructor ever!
@hobbyhub
3 жыл бұрын
Jon, how can we have total of saleamount against each salesperson by sum up with unique names of salesperson. for example i want to know that which salesperson did a total amount of sales ? How to compile these results ? Please reply
@DoItTennis
2 жыл бұрын
@Hobby Hub, did you find an answer to this? I am looking for the same function and don't even know how to ask in a way that brings up a search result. lol.
@excelisfun
6 жыл бұрын
Thanks, Jon!!!
@chakrihhp
3 жыл бұрын
Thanks its really good nice explained
@jamaljoudeh6625
2 жыл бұрын
Thank you
@jocebelrivera6802
Жыл бұрын
Thank you for the help
@nalinikantamahapatra3944
3 жыл бұрын
Thank you easily solutions.
@michaelyoo5474
4 жыл бұрын
I love your videos. Thanks for uploading these
@escribidormadrid3463
3 жыл бұрын
Thanks very much.
@omarrodriguez8226
6 жыл бұрын
Great video, It really works !! Thank you.
@ankitasharma5702
5 жыл бұрын
Question A unique value is fine but what if we need the sum corresponding to the unique value same as in seen in your video the name Mariya Sergienko has 2 common dates 1-4-2014 but with the different Unit price is there any way where the unique data value display the sum automatically
@a.f.mohamedrijah932
Жыл бұрын
thanks ❤
@DougHExcel
6 жыл бұрын
Thanks for the video!
@emad2
4 жыл бұрын
Go on . Fantastic
@nathiraparveen5504
2 жыл бұрын
Hi John, what if the column has multiple text with a comma and I want still want to identify all unique values under a column?
@sameemsarkar8578
3 жыл бұрын
Thanks, man!!
@rrcablenetwork2057
2 жыл бұрын
Thank you jon
@geronymo28
2 жыл бұрын
In this case how to remove from all excel the duplicate names still empty without delete the rows. For example I want to delete 2 of three same name andrew cencimi
@Bobcat8746
2 жыл бұрын
Thank you!!!!!!!!!!!
@VikasSingh-em3ol
2 жыл бұрын
Nice video. Super video. Question- I have data in 6 column and data are same in all columns (data like- B200089NT67) when I select data range and apply condition formatting duplicate then all duplicate highlights but when go to duplicate remove option and apply then I find error "No duplicate find value" so how to solve it. I had also checked all data extra space using Trim and also checked cell value is correct. Example- Column1 Colum2 Column 3 DR000257DT TR56884QR ASG489901 DR007657DT
@sahilreddy287
3 жыл бұрын
thnx brother
@Ramizamel208
3 жыл бұрын
this is amazing , thanks :)
@zacsofarjustfriends
4 жыл бұрын
Thank you!
@saifulshahin3048
3 жыл бұрын
What should i do if i want to remove my duplicate and sum my duplicate values
@DoItTennis
2 жыл бұрын
Did you find an answer this?
@maheswarat1423
6 жыл бұрын
Thank you sir, Good, clear, Detailed explanation
@LarsIngeHolen
3 жыл бұрын
What if I like to use the filtered data for a named range that is updated every time there is change in the table.
@sacramentorealestateagentp3764
4 жыл бұрын
First name in Exel is Russian, in fact I had client who's name was Sergienko :) But that was back in Minnesota.
@somnath4245
3 жыл бұрын
will this macro treat 2 values with 1st half same as duplicates ? EXAMPLE - Name List has 2 names of different people starting with similar values - "John" vs "John Wick" The "Remove duplicates" function removes the John as duplicate
@nayyarnayyar1560
2 жыл бұрын
Remove duplicates and totals value of such duplicate maintain Sam - 80 $ Sam- 90 $ Jack - 100 $ Jack - 120 $ Jack - 110 $ Mike - 49$ Mike - 5$ Mike - 6$ Mike - 8$ Output like this Sam- 170$ Jack - 330 $ Mike - 23$
@ashoksahu9546
4 жыл бұрын
Excellent video.
@dinutintu
6 жыл бұрын
Nice video... thanks for sharing
@YourKabbalahCenter
2 жыл бұрын
Hello, Is there any possibility to remove semi-duplicate titles? (ex- "Why cats sleep all day?" & "Do cats sleep all day long?).....those 2 titles basically mean the same, however, I am searching for a tool that can come up with let's say 80% duplication. Thanks in advanced.
@rajukumarvishwakarma2067
4 жыл бұрын
Thanks
@suspicious.tofuuu
3 жыл бұрын
Hi, is there a way to remove duplicated columns (i.e. i have >1 column that have the exact same data) instead of duplicated rows?
@leviskariuki6174
3 жыл бұрын
is there a way to remove duplicates and add some of its data back to the existing data?
@beauterrence9506
6 жыл бұрын
Thanks for the video 😘😘
@oumaimasounnielalami8762
3 жыл бұрын
Ask for help on Excel VBA📍 I have two columns A and B. The column A has "material's name" and the column B has "different date" There are duplicate "material's name" and I want to remove these that have The same date.
@telecomupdate7820
4 жыл бұрын
Nice video
@haifamansour24
4 жыл бұрын
Thank u !
@sudeshsrivastava1435
2 жыл бұрын
Excel sheet start malfunctioning when we enter data in more than 50 lakh rows aprox. Means per excel sheet don't have capacity to contain more than 50 lakhs rows aprox. data. Now the question is how to remove duplicates in excel if data is in different excel workbooks and the total rows in all excel workbooks are more than 20 crore. Can any body provide solution?
@sherazahmed7084
Ай бұрын
which excel version your are using
@ponnuruprabhu9264
3 жыл бұрын
good
@topchoice32
5 жыл бұрын
What if u have to list to compare to find duplicates and leave the new names
@JV-xg5jk
5 жыл бұрын
Also what if one colum it says Mariya, another Mariya, Gina, Sarah, and we want to remove Mariya from the first column?
@MuneerAkl
5 жыл бұрын
Thanks for sharing Jon
@sajeevvillody
6 жыл бұрын
HI i need to delete two same names, i use duplicate , but it remove only one name , i need to remove both names . Example ( boby, steve ,jeff, steve , evan ,eric ) need to delete both steve names . any formula please.
@lolasimmons8780
6 жыл бұрын
Thanks for the video;)
@ma33139
3 жыл бұрын
Is there a way to highlight and/remove duplicated names between 2 separate column and in separate order? for example, column1 "Ann Marie Sally" column 2 "Sally, Sue". These columns would be highlighted because the word "Sally".
@SIMRANKAUR-eg7nc
3 жыл бұрын
Use vlookup with search or match whatever suits u as there is an option of approximate match. Create a formula first in any cell ,say, want to search A1 in B1 and u r applying thar formula C1. When it works for one particular data, copy thay fomula and then go to conditional colour fomating and select the new formula tab and paste ur formula there. U will be given formating option like by which colour it is to be highlighted kind of thing.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source or some pdf files.
@maythamalmouthen5191
4 жыл бұрын
Many Thanks, Great work. Please advice If there is any formula to remove duplication names in one cell of excel sheet which are separated by ",".
@SIMRANKAUR-eg7nc
3 жыл бұрын
For comparing or removing duplicate data we at least need to remove extra spaces and other unwanted characters and there is a greater possibility of comparison between twos sets of data. I therefore would suggest to first trim clean the given set of data. Party from this by using find and replace you can get rid of comma @ or another spacial characters or even space. So use find replace to get rid of those and then proceed.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source or some pdf file.
@kareemaldabbas4517
4 жыл бұрын
super
@mohamedchakroun4973
6 жыл бұрын
Thank you jhon. How can i upload new icons to excel to use them as symbol in macro???
@sanjayjaiswal4261
4 жыл бұрын
GOOD
@cypheraine17
5 жыл бұрын
What if I want to do it on the same sheet instead of creating a new one?
@DarkSpeedKiller
4 жыл бұрын
Hello.Im new on excel and have never used it before.What i wanna do is make a collumn into a list without diplicates in another sheet because i need to use it with a sumif because it has certain prices with those dates.for example i want my list to have only once the date 1/1/2001 and the sumif will show 100000.I dont want multiples 1/1/2001 since im already have lots of other dates.Ive seen other videos where they copy the collumn paste it and the remove duplicates but i dont wanna have a 2nd copy of that somewhere else in my sheet.What ive done for it to work is while i make the list instead of choosing cells i just typed the dates.Thats not dynamic at all and really bad in my opinion. Any help would be appreciated
@SIMRANKAUR-eg7nc
3 жыл бұрын
My suggestion here will be to apply normal filter or pivot. But while using sumif or countif use =subtotal instead where u r given at least 10 to 15 formula under it. The advantage using it is thay when u filter ur data om some parameters and it refines u to 10 data say, the formula will be applied to those 10 only unlike the normal countif or sumif
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source or some pdf files..
@alixgomez726
4 жыл бұрын
how do I delete duplicates with name and Lastname columm
@kethanlagishetty6175
5 жыл бұрын
Hi Jon, How can we remove case sensitive duplicates items
@JV-xg5jk
5 жыл бұрын
How do we do it of values are in rows?
@timungasangho
6 жыл бұрын
How to extract salary records using auto filter.
@moesadr3342
4 жыл бұрын
Hey Jon, Awesome video, thank you. I have one question since I need to do this in several worksheets in one workbook. Is there any way that you could alter the code that no matter how many times we run the macro for different fields, the list will be created in one a designated worksheet only but different columns. Lets say we create a sheet " i.e sheet 10". Then we tell the macro to copy the unique list of the selected column/s to a certain column/s on sheet 10?
@moesadr3342
4 жыл бұрын
@Dave Page No I have not.
@UrduWithAhmadRaza
2 жыл бұрын
quickly remove all empty and duplicate rows and column in MS WORD
@juergenschubert3247
6 жыл бұрын
how owuld this work when you just have an erea not a whole column. I have an range of 4 columns and 7 lines but not each cell is filles with a text . Actually the domain of an email address. Now I need this block in a list and remove the duplicates
@SIMRANKAUR-eg7nc
3 жыл бұрын
Select the entire data first and use remove duplicate, all columns will be shown to have been selected. U unselected others and select only the one where u want to find the duplication.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source or some pdf file.
@pinnacledesignsllc8457
4 жыл бұрын
My ribbon does not have the "My Macros" tab, what do I need to do to add it to my ribbon?
@giselleescobar1447
3 жыл бұрын
options - customize ribbon - tick developer
@slowrook
6 жыл бұрын
Hi, i really appreciate if you help me with some excel skills am working on huge data and i need your help
@Sinahdlngs
4 жыл бұрын
Jon I have a very specific and complicated Excel question for my business that I need help with. How can I get in touch with you? I'm happy to compensate you. Thank you.
@SIMRANKAUR-eg7nc
3 жыл бұрын
Search channel "off to office". You will find unbelievable methods of preparing raw data copy pasted from any web source or some pdf file.
@moonlightsonata8176
3 жыл бұрын
How to undo remove duplicates?
@JAS82
4 жыл бұрын
why not use the "=unique()" function?
@SIMRANKAUR-eg7nc
3 жыл бұрын
Cos bro these unique and filter functions are available with office 365 only.
@jcare07
3 жыл бұрын
👍
@shamrozanjum
2 жыл бұрын
Hi Jon, I want to find duplicates from 2 lists and paste the found duplicates in another sheet. How can I do that?
Пікірлер: 104