Thank you Leila! Another great lesson to start my Thursday morning! Knowing about the case sensitive aspect of each method is so helpful. You are an awesome teacher!
This is the best explanation for removing duplicates!! Thanks, A Lot Your intros are always so attractive and so are your thumbnails, could you please make a tutorial on that.👏👏👏👍🔥🔥👍👍👍
@@LeilaGharani Thanks So Much!!! That would be delightful😀👏👏. Could you please give me a shoutout for giving the idea for the video? My channel = Nish n Neil Rock!! Thank You
Hi Leila, under my F file folder, a main folder contained hundreds of sub folder, in it are Thousand of files & folders. my target is to delete only all Excel, Word, PDF, anyway i can start from my main folder and make setting here so that it can be delete all in once, avoiding go thru 1 by 1 files deleting action. Hope to hear from you. Tks
Hi there big fan here . I have a question for you I have a worksheet that have 8 different spreadsheet. Within the spreadsheet I have multiple column but I want to pull the duplicate out out of column B on all the sheet . I can do it by conditional formatting but this only works within the same spreadsheet. I want to pull out of 8 different spreadsheet within the same workbook like I said . Can you help me .? And can you make a video doing something similar .. thank you and keep them videos coming .
great info, thank you for sharing. quick question, do you do any project work for hire...for example, if we wanted to hire you to create a specific process for removing duplicates and culling out certain lines of data (instead of dragging the cursor), would doing a screen share tutorial be something that would be possible to hire you for? not ongoing management of the process....unless that is an option, but just teaching a team member to do this specific process and screen record so we can use as a tutorial going forward.
How about displaying the duplicates that were found and removed? I routinely use files that have 10-25k rows so being able to see what was removed would be very helpful. Great topic!
Duplicates with a lot of decimals. I was having trouble removing duplicates because of the decimals in some of my duplicates were very far to the right and it looked like the same number but it was different. Rounded the decimals and then I was able to remove duplicates.
I use remove duplicates a lot but I think I like the unique fx better. Power query is great for big data sets. Another great tutorial for daily use in excel. TFS! Have a great day!
Awesome techniques to remove duplicates! Pivot tables are another way to remove duplicates because they de-duplicate items in the rows and columns pane.
Thank you for sharing your experience 😊 Uniqe function is available also in excel 2021. I like powerquery version, but how can I update it automatically? 🤔
I used to be an Excel expert. After having discovered your channel I am convinved I must take a course of some sort. Never realised it developed so much.
Thanks Leila for the interesting lesson :-) One little note about PQ case - you don't need to add a step..... you can add second argument of Table.Distinct function. In this case it will be something like this below. = Table.Distinct(#"Changed Type", Comparer.OrdinalIgnoreCase)
Hi Leila. Great tips for removing duplicates from a data set. A fourth option is to use Advanced Filter and check "unique". These days, the UNIQUE function and Power Query are the goto methods for maximum flexibility and for dynamic results. Good to know many methods though. Did I get the job? Haha!! Thanks for the video :)) Thumbs up!! PS - Great reminder about case sensitivity in Power Query vs. other methods.. easy to forget that distinction.
That's Great Leila, Thanks for the video. Kindly help us to remove duplicates in a file, i am stucked in, Here is the case- Column A have: 10 values Column B have : 8 values (In B - 2 values are new and 6 values are same already present in "A") Now, without disturbing Colum A values, What is the method to remove those 6 duplicate values from B)
Hi Leila. I am your top most fan. I like your way of expression, sharpness, dedication and subject matters. I want to connect you somehow and that is why I subscribed your channel. You are true beauty.
Great video. Can you help me with the formula for highlighting 5 consecutive duplicates, for example a raw from A1 to O1, i want to identify and highlight if i have 5 consecutive duplicates with the same value of "P".
Hi, unique formula did not work on a 232 rows/28 column table.resource error shown. But if column number is reduce, then the formula worked well. So, my question is, what is the size limit for a table to be use with this formula? Thank you in advance.
Hi Is it possible to only find and highlight a specific amount of repeating values? I have a huge list of repeated numbers that start at 0 and go as high as 9999. I already had Excel sort them in ascending order. Many of the numbers repeat about 4 to 6 times but I'm only interested in highlighting the ones that repeat 10 times or more, is this possible to do? Thanks
There is one more more manual but sometimes handy way to spot duplicates via conditional formatting, it will highlight your duplicates so you can see if there are duplicates and you can decide what to do about them.
Hello, thank you for all your videos. Many use existing files as templates þo create new. Example is purchase or delivery orders etc. Over time the size of the files becomes huge and slow to open. Copying cells and pasting to another new excel workbook destroys the format. Can you suggest a workaround or make a video for this? Will be most useful to many.. Thank you in advance on everyone's behalf. Regards.
I have a long list of Barcodes and names, the issue is with all methods I cant choose which Duplicate to remove! there a barcode that had a correct name and others do not. I want to remove the ones I want in a 20k row sheet.
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 DR000257DT
I know the 3 methods for removing duplicates value But I don't know what are the difference between these methods Thank you liela .👍 You are amazing 👏❤
How may one remove duplicates found inside a cell? Ex. B3 contains Dave, John, Fred, Sam, Sam, Steve & Dave. How may Dave and Sam duplicates be removed? Thank you, Rolland.
Excellent explanation and new ways to delete duplicate data. Thank you for your efforts. We are waiting for more videos and new ideas. I am a good follower of your channel and I am from Egypt Greetings to you
I really enjoyed the video. I had an problem to solve with some comma separated data and I couldn't figure it out in Excel. I had to write a custom PowerShell script to get what I needed. If I had data like this, how could I build a string of the column 2 data if column 1 was a duplicate? 1,apple 1,cherry 1,banana 2,red 2,blue 3,circle 4,dog 4,cat I would like to see: 1,apple,cherry,banana 2,red,blue 3,circle 4,dog,cat
Sure, you have exact duplicates, so how would you remove things like RUclips and RUclips.com, or ABC and ABC, INC, or ABC, Inc. All of these are duplicates but they do not have the exact same string. Also what about Conditional Formatting remove duplicates?
Hi, I am learning MS Excel now, which exam i need to take firstly on excel to get certificate from microsoft since there are multiple excel courses with same code, could you please suggest me which exam(2013/2016/2019) I need to take firstly on excel since I am a beginner
How to remove duplicates data from cell with blank adjacent cell. Ex. In cel A1 and A2 have same data. However in cell B1 is blank and B2 has data. So duplicate data from Column A should get delete with blank adjacent cell of B1.
I need help with something please 🥺. I want a filter that 'latches' a result - for example I have a column of IDs. I want the corresponding row to say turn red when user ID x logs in...then remain lit for every row beneath it, until same ID x logs off... Someone knows? I don't want to do it in a macro
Hi Leila ,how to find frequency (gap between next entry) of same item in a month/ year with interval of time ie. Perticular product sell some qty 10 times in each 5- 6 days or days gap time. Same should vonvert in graph.
I have A column values and B columns values comparison, in C column I want the missing values in it for example A1 cell contains abc, def, ghi and B1 contains abc, def so if I compare both cells, missing string "ghi" will be shown in C1
it looks like the data has to be a table instead of a spreadsheet formatted to look like a table. That was the only roadblock I dealt with. In the case of contacts. Do you have any suggestions for identifying a duplicate in the data and merging the data to keep the unique data intact from both sources??
You have your excel set up already. Can you show us how to add or set up everything before you begin? How to add From Sheet to Get & Transform Data? Thank you for your videos..
It would be nice if the filter function filters the duplicates first, then highlight with color automatically and show the rows. Sometimes we don't want to delete the duplicates right away, we need to see first and then decide what's really duplicate. In the meantime I have to use the conditional formating plus the countifs, etc
how bout removing dups from one column?? and also how would you freeze the results so they remain in the row you insert them in rather then consolidating up? Please advise @leila , thanks.
Amazing !!! I never ever noticed this (even i face issues when removing duplicates) I m using excel for last 26 years, but when i see your videos i feel i m beginner yet. :) Thanks for sharing .....
Anybody found a way to deal with typos and user idiosyncrasy e.g. using "North America" in one row then "NA" in the other? Until then I shall remain a data janitor.
Hi, do you have a video on sorting multiple tables in a sheet on 1 column value without changing the rows in a table ? It’s like treating each table as 1 row and assumes each table has the same row labels
Grab the file I used in the video from here 👉 pages.xelplus.com/remove-duplicates-methods-file
Knowing that Power Query is case sensitive will be important to remember. Your t-shirt and bracelet combination were captivating.
Thank you 😊
Pls make a video on finding, keeping and distinguishing between duplicates. 😊
My solute to your valuable solutions/suggestions
Thank you!
Great Point with PQ, Change the 'Format' simple but effective. Thanks for this tip
Thanks, i´m just clean some data for my university homework :) you help me a lot
Thank you. Well explained
One of the very talented person who deserves more subscribers
Thank you Leila! Another great lesson to start my Thursday morning! Knowing about the case sensitive aspect of each method is so helpful.
You are an awesome teacher!
You're so welcome, Jay!
Jay holm,, where are you from?
Very helpful video once again Leila. Thank you
My pleasure!
Thanks for the quick and easy tutorial! you just saved me a ton of time!
😍 thank you Leila you always save my life
Thanks a lot man u made my life simple
Great Info Leila. Do you have any info on how to "Extract" those duplicates into another table instead of removing them?
Thanks Leila. Always Learning. :) :)
Thank you Leila for another excellent presentation. Another method of removing duplicates is using Conditional Formatting and filtering by color, no?
👍👍👍
It would help to know the impact of performance of using these. Especially unique function, because it seems to trigger for any cell edit.
Very helpful!
I Like you voice and speaking skills
Hope you focus on content too
@@thenotidevil..... Like you are focusing on other people comments 😀
thank you very much.. your videos are really helping me at work.
Glad you like them!
This is the best explanation for removing duplicates!! Thanks, A Lot
Your intros are always so attractive and so are your thumbnails, could you please make a tutorial on that.👏👏👏👍🔥🔥👍👍👍
Thank you! Will think about it.
@@LeilaGharani Thanks So Much!!! That would be delightful😀👏👏. Could you please give me a shoutout for giving the idea for the video? My channel = Nish n Neil Rock!!
Thank You
It’s very nice video, especially when you are dealing with a lot of data
Glad you like it.
Thank you so much, great lesson!
Great work as always. Your videos have helped me a ton. Any chance you could do a series on Power BI??
Super useful
You are the best
Superb your Video
Advanced Filter; Pivot Tables (Data Model for distinct list).
Amazing ❤! Thank you very much!
Always Super
I like unique function
I know I'm an excel nerd because this was riveting
Grazie
Hi Leila, under my F file folder, a main folder contained hundreds of sub folder, in it are Thousand of files & folders. my target is to delete only all Excel, Word, PDF, anyway i can start from my main folder and make setting here so that it can be delete all in once, avoiding go thru 1 by 1 files deleting action. Hope to hear from you. Tks
Hi there big fan here .
I have a question for you I have a worksheet that have 8 different spreadsheet.
Within the spreadsheet I have multiple column but I want to pull the duplicate out out of column B on all the sheet .
I can do it by conditional formatting but this only works within the same spreadsheet.
I want to pull out of 8 different spreadsheet within the same workbook like I said .
Can you help me .?
And can you make a video doing something similar .. thank you and keep them videos coming .
And sometimes: Sort & Filter group / Advanced (advanced filter), PowerPivot (rows), filter by column with COUNTIF(S) function
Is there away to keep the duplicates removed?
GOOD BUT HOW TO REMOVE DUPLICATE FROM LANDSCAPE ROW
Can we do distinct sum??and how
how to remove old entire rows with the duplicates from the latest row. i mean how to retain the latest rows and the old rows should be remove?
great info, thank you for sharing. quick question, do you do any project work for hire...for example, if we wanted to hire you to create a specific process for removing duplicates and culling out certain lines of data (instead of dragging the cursor), would doing a screen share tutorial be something that would be possible to hire you for? not ongoing management of the process....unless that is an option, but just teaching a team member to do this specific process and screen record so we can use as a tutorial going forward.
While using 2nd method I’m unable to see the arrow please help me out
Is it still possible to use Advanced filter to this?
Yes!
All Hail Queen Leila!👸🙏
How about displaying the duplicates that were found and removed? I routinely use files that have 10-25k rows so being able to see what was removed would be very helpful. Great topic!
Duplicates with a lot of decimals.
I was having trouble removing duplicates because of the decimals in some of my duplicates were very far to the right and it looked like the same number but it was different. Rounded the decimals and then I was able to remove duplicates.
I use remove duplicates a lot but I think I like the unique fx better. Power query is great for big data sets. Another great tutorial for daily use in excel. TFS! Have a great day!
Awesome techniques to remove duplicates! Pivot tables are another way to remove duplicates because they de-duplicate items in the rows and columns pane.
Thank you for sharing your experience 😊
Uniqe function is available also in excel 2021.
I like powerquery version, but how can I update it automatically? 🤔
I used to be an Excel expert.
After having discovered your channel I am convinved I must take a course of some sort. Never realised it developed so much.
Dont know how to search for it but how can export item from one site go direct to my excel
you can use pivot tables also to remove duplicates
Thanks Leila for the interesting lesson :-)
One little note about PQ case - you don't need to add a step..... you can add second argument of Table.Distinct function.
In this case it will be something like this below.
= Table.Distinct(#"Changed Type", Comparer.OrdinalIgnoreCase)
3 EASY Ways... ;)
Hi Leila. Great tips for removing duplicates from a data set. A fourth option is to use Advanced Filter and check "unique". These days, the UNIQUE function and Power Query are the goto methods for maximum flexibility and for dynamic results. Good to know many methods though. Did I get the job? Haha!! Thanks for the video :)) Thumbs up!! PS - Great reminder about case sensitivity in Power Query vs. other methods.. easy to forget that distinction.
You sure did, Wayne 😁
That's Great Leila, Thanks for the video.
Kindly help us to remove duplicates in a file, i am stucked in, Here is the case-
Column A have: 10 values
Column B have : 8 values (In B - 2 values are new and 6 values are same already present in "A")
Now, without disturbing Colum A values,
What is the method to remove those 6 duplicate values from B)
Thanks. This was great. You can also use Conditional formatting to identify duplicates then remove them.
Great vid as always. Every time I watch one of your videos, I learn something new or a better way to do something.
Glad to hear it!
@@LeilaGharani can we extract data with the help of advanced filter
@@LeilaGharani can we use conditional formatting
@@LeilaGharani can you reply right now
Hi Leila. I am your top most fan. I like your way of expression, sharpness, dedication and subject matters. I want to connect you somehow and that is why I subscribed your channel.
You are true beauty.
Great video. Can you help me with the formula for highlighting 5 consecutive duplicates, for example a raw from A1 to O1, i want to identify and highlight if i have 5 consecutive duplicates with the same value of "P".
Hi, unique formula did not work on a 232 rows/28 column table.resource error shown. But if column number is reduce, then the formula worked well. So, my question is, what is the size limit for a table to be use with this formula? Thank you in advance.
Hi
Is it possible to only find and highlight a specific amount of repeating values? I have a huge list of repeated numbers that start at 0 and go as high as 9999. I already had Excel sort them in ascending order. Many of the numbers repeat about 4 to 6 times but I'm only interested in highlighting the ones that repeat 10 times or more, is this possible to do? Thanks
For some unknown reason excel showing unique values as duplicates for first 100 cells of the column. Pls help 😣.
There is one more more manual but sometimes handy way to spot duplicates via conditional formatting, it will highlight your duplicates so you can see if there are duplicates and you can decide what to do about them.
but removing manually it's more time taking, if you have larger data😢
Hello Leila, Thank you for this tutorial ^_^. I got a question though, how about if I just want to hide value and not remove it? Thank you
Hello, thank you for all your videos.
Many use existing files as templates þo create new. Example is purchase or delivery orders etc. Over time the size of the files becomes huge and slow to open. Copying cells and pasting to another new excel workbook destroys the format.
Can you suggest a workaround or make a video for this? Will be most useful to many.. Thank you in advance on everyone's behalf. Regards.
Exactly what I needed
Thank you!
I have a long list of Barcodes and names, the issue is with all methods I cant choose which Duplicate to remove! there a barcode that had a correct name and others do not. I want to remove the ones I want in a 20k row sheet.
I've always relied on your videos everytime I want to learn something new in Excel. Thanks!
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 DR000257DT
I know the 3 methods for removing duplicates value
But I don't know what are the difference between these methods
Thank you liela .👍 You are amazing 👏❤
Can we do some filter/sort or use formula to identify positive & negative matching value
Like
+100
-150
+125
+156
+176
+150
-176
-100
-156
-166
How may one remove duplicates found inside a cell? Ex. B3 contains Dave, John, Fred, Sam, Sam, Steve & Dave. How may Dave and Sam duplicates be removed? Thank you, Rolland.
Excellent explanation and new ways to delete duplicate data. Thank you for your efforts. We are waiting for more videos and new ideas. I am a good follower of your channel and I am from Egypt Greetings to you
I really enjoyed the video. I had an problem to solve with some comma separated data and I couldn't figure it out in Excel. I had to write a custom PowerShell script to get what I needed.
If I had data like this, how could I build a string of the column 2 data if column 1 was a duplicate?
1,apple
1,cherry
1,banana
2,red
2,blue
3,circle
4,dog
4,cat
I would like to see:
1,apple,cherry,banana
2,red,blue
3,circle
4,dog,cat
Sure, you have exact duplicates, so how would you remove things like RUclips and RUclips.com, or ABC and ABC, INC, or ABC, Inc. All of these are duplicates but they do not have the exact same string. Also what about Conditional Formatting remove duplicates?
Hi, I am learning MS Excel now, which exam i need to take firstly on excel to get certificate from microsoft since there are multiple excel courses with same code, could you please suggest me which exam(2013/2016/2019) I need to take firstly on excel since I am a beginner
How to remove duplicates data from cell with blank adjacent cell.
Ex. In cel A1 and A2 have same data. However in cell B1 is blank and B2 has data. So duplicate data from Column A should get delete with blank adjacent cell of B1.
Hi, how do you check and remove duplicates between 2 sheets? Thanks
I need help with something please 🥺. I want a filter that 'latches' a result - for example I have a column of IDs. I want the corresponding row to say turn red when user ID x logs in...then remain lit for every row beneath it, until same ID x logs off... Someone knows? I don't want to do it in a macro
I have this type of sheet one..
Hi Leila ,how to find frequency (gap between next entry) of same item in a month/ year with interval of time ie.
Perticular product sell some qty 10 times in each 5- 6 days or days gap time.
Same should vonvert in graph.
I just wanna say a huge Thank You for your video And Tbh you're one of the most professional people I've ever seen niche :)
hi, how to find the reverse duplicate between columns like an example to consider JFK AP the duplicate for AP JFK
please your help.
I use countifs(A$2:A2,A2,B$2:B2,B2.....) to count the occurence and remove everything except the first occurence.
I have A column values and B columns values comparison, in C column I want the missing values in it
for example A1 cell contains abc, def, ghi and B1 contains abc, def so if I compare both cells, missing string "ghi" will be shown in C1
it looks like the data has to be a table instead of a spreadsheet formatted to look like a table. That was the only roadblock I dealt with. In the case of contacts. Do you have any suggestions for identifying a duplicate in the data and merging the data to keep the unique data intact from both sources??
You have your excel set up already. Can you show us how to add or set up everything before you begin? How to add From Sheet to Get & Transform Data? Thank you for your videos..
Col-A2 & A3 has user ID=1, Cols-B2 & B3 has Ref. Nos. 1245 & 12459
I want a formula to get the max Ref. No. for user ID 1
It would be nice if the filter function filters the duplicates first, then highlight with color automatically and show the rows. Sometimes we don't want to delete the duplicates right away, we need to see first and then decide what's really duplicate. In the meantime I have to use the conditional formating plus the countifs, etc
There is a FIND DUPLICATES function that combines with conditional Formatting to do exactly what you describe.
Is there any reason we should copy the value first? Why can't we just remove duplicate within the data filter applied?
Hi Leila, how to highlight duplicate values that are more than 15 characters eg. serial numbers in a single column?
how bout removing dups from one column?? and also how would you freeze the results so they remain in the row you insert them in rather then consolidating up? Please advise @leila , thanks.
Hi Leila,
How to Remove Duplicate videos and another stuffs from computer? Thank u! Leila :)
Need to find duplicate in two sheets having with three colums same colums... to find missing data....🤔🤔🤔
Your a Guyanese or Trini Girl like me , new friend 😊😊
Learned to solve things much easier with your Videos 👍
I used adodb and a group by query in vba for this before.
Amazing !!! I never ever noticed this (even i face issues when removing duplicates)
I m using excel for last 26 years, but when i see your videos i feel i m beginner yet. :)
Thanks for sharing .....
Anybody found a way to deal with typos and user idiosyncrasy e.g. using "North America" in one row then "NA" in the other? Until then I shall remain a data janitor.
hi, since I'm working with voluminous data. May I know if where can I see the removed duplicates so I can double check faster? thanks in advance
Hi, do you have a video on sorting multiple tables in a sheet on 1 column value without changing the rows in a table ? It’s like treating each table as 1 row and assumes each table has the same row labels
i use MS 365, and i think the UNIQUE formula is the best thing i shuld try