What if Excel saved you 10 hours every week? Join my FREE webinar on Thursday, Jan 16, to learn 3 secrets to work less and achieve more. Spots are limited-grab yours now! 👉 event.webinarjam.com/register/13/roovru4
She is hands down the best person for excel problems. It's the way she explains it. Let's face it, when you come to her videos, you usually have a problem, and you're usually on a deadline. She gets right to it.
This showed on my feed at the perfect time. I'm going to be applying your last example to a data set with around ~10,000 rows today. This will make it so much easier that what I normally do. Thanks!
'Thursday's with Leila' just doesn't disappoint ... even when not immediately useful, the videos still plant ideas for future harvest ... 🌱🌿🌿🌱🌱 At the 8:20 mark " ... I hope you enjoyed this video ... don't forget to hit that 'thumbs up ... " . There should be another option; 'Two Thumbs Up' ... 👍👍 Thank you ... thank you ... thank you ... 😍😍😍
Great video.... be careful when combining columns when looking for duplicates. I recommend putting a delimiter between the columns to help eliminate false duplicates. If you have the values AA BB two columns of data in the first row, and A ABB as the second row, they will end up giving you a false duplicate of AABB. Use a "-" as a delimiter to help remove this possibility. AA-BB won't get confused with A-ABB.
fantastic video, straight to the point. thank you very much Highlight column(s)/cell(s), click Home at the top > Conditional Formatting > Highlight Rules > Duplicate Values
Thank you for your useful conclusion~ same, I'd like to share another quick way to find duplicates with you, and hope it can be helpful too! you just need to open your file in WPS Office> select the range of your values>click “highlight duplicates”> click “set” then all the dupplicates will appear!
This video is great, thanks for making it. You have shown us that "there is no need to invest in costly analytic tools" through your use of Excel for data analysis. That's fantastic, you have my undying loyalty.
Very much helpful to get improved the XL hacks. Thanks Leila. Always watching you. Your presentation of Videos is super marvelous. I got much knowledge from you. Thanks again Leila.
First ever video of Leila when I finally did not learn anything new :D I am not disappointed though- I was already wondering if there will be ever video when I will already be 'know it all' lady :D :D :D
Thank you Leila for this video on the most common need, find duplcates and the four cool ways to get the job done! I have watched your videos over a period, and you have come a long way in terms of presentation, quailty and utility of the videos, that's getting superior by the day! Good luck to you!
I know this is a little off topic, but for anyone removing duplicates in power query for excel or power bi, there is a trap. Yes, just like in an excel spreadsheet the first unique row found is retained while others are removed. However, the way that first row is defined is the trap. It is not defined as the first row as the data is currently, but the first row when the data was first loaded. So you might do a sort to ensure the rows you want retained are at the top then remove duplicates but this will not give you the correct result. The solution is to include Table.Buffer in the remove duplicate query to reset the state of the data to the current sort order. Then the power query remove duplicates operation will work the same as if you removed duplicates from an excel spreadsheet.
Its a Discovery.. Thank You for the Valuable Information. I Have Been Using CountIf with start cell as Absolete and 2nd Cell as Relative in a Range. A1:A100 Has Unique and Duplicate Values Formula For B2 =CountIF($A$2:$A2,$A2) Formula For B3 =CountIF($A$2:$A3,$A3) Formula For B4 =CountIF($A$2:$A4,$A4)
Love how you casually added the emoji, but you just created a monster. I'm going to use an emoji in every formula from now on, just to make my day a little more enjoyable. Chances are, I'll be using the middle finger quite often.
It's always a treat to watch and learn from your videos. Just want to know how to create dummy data and query the data to apply formulas in different ways. If you can make a tutorial on same that would be great and I assume maximum viewers have similar concern while practicing ... thanks
Thanks Leila for the video and for always keeping us abreast with excel advancements. Wondering if there's a formula for highlighting figures that 'knock off each other' in a column. Let's say 12 and -12 or -1,000 and 1,000? Thanks
In the last exercise to pull out just the duplicates, an alternative is to use UNIQUE instead of SORT in case you don't want to see the same record multiple times over.
What if Excel saved you 10 hours every week?
Join my FREE webinar on Thursday, Jan 16, to learn 3 secrets to work less and achieve more.
Spots are limited-grab yours now! 👉 event.webinarjam.com/register/13/roovru4
how about if you need to compare from two different excel files?
Really helpfull thank you
She is hands down the best person for excel problems. It's the way she explains it. Let's face it, when you come to her videos, you usually have a problem, and you're usually on a deadline.
She gets right to it.
Thanks!
Thank you!
This showed on my feed at the perfect time. I'm going to be applying your last example to a data set with around ~10,000 rows today. This will make it so much easier that what I normally do. Thanks!
'Thursday's with Leila' just doesn't disappoint ... even when not immediately useful, the videos still plant ideas for future harvest ... 🌱🌿🌿🌱🌱
At the 8:20 mark " ... I hope you enjoyed this video ... don't forget to hit that 'thumbs up ... " .
There should be another option; 'Two Thumbs Up' ... 👍👍
Thank you ... thank you ... thank you ... 😍😍😍
ahhh thank you for the two thumbs up. Appreciate it.
Great video.... be careful when combining columns when looking for duplicates. I recommend putting a delimiter between the columns to help eliminate false duplicates. If you have the values AA BB two columns of data in the first row, and A ABB as the second row, they will end up giving you a false duplicate of AABB. Use a "-" as a delimiter to help remove this possibility. AA-BB won't get confused with A-ABB.
Thanks for the suggestion, John
Thanks Leila! exactly what Im looking for. Very timely. 3thumbs up!!!
It never came to my mind to add emojis in cells! That's really cool and opens up many possibilities to make the sheet more appealing 😊
fantastic video, straight to the point. thank you very much
Highlight column(s)/cell(s), click Home at the top > Conditional Formatting > Highlight Rules > Duplicate Values
Thank you for your useful conclusion~
same, I'd like to share another quick way to find duplicates with you, and hope it can be helpful too!
you just need to open your file in WPS Office> select the range of your values>click “highlight duplicates”> click “set”
then all the dupplicates will appear!
Leila you are an angel!!!! thank you so much!
This video is great, thanks for making it. You have shown us that "there is no need to invest in costly analytic tools" through your use of Excel for data analysis. That's fantastic, you have my undying loyalty.
Glad you enjoyed it!
Super helpful! Thank you!
Just GORGEOUS. The lady and the formulas, Thanks for sharing once again. :)
Excellent guidance and this will help me so much in my work as I've to clean up ~21k rows of data. Thanks Leila.
Great video. This helped me after watching all other videos. A life saver!
Finally! I’ve been looking for a video like this for so long! I’ve had the idea but I didn’t know how to start, thanks!!
Glad I could help!
That ConCat formula is a game changer. THANK YOU
Very much helpful to get improved the XL hacks. Thanks Leila. Always watching you. Your presentation of Videos is super marvelous. I got much knowledge from you. Thanks again Leila.
Good video! Your tips are a great way to keep me updated with excel. Thanks for sharing.
My pleasure, Audrey!
First ever video of Leila when I finally did not learn anything new :D I am not disappointed though- I was already wondering if there will be ever video when I will already be 'know it all' lady :D :D :D
🙌
OMG you are ALWAYS the best!!! Thank you!!!
Leila has saved my life 1000000000000000x thank you!
Thank you Leila for this video on the most common need, find duplcates and the four cool ways to get the job done! I have watched your videos over a period, and you have come a long way in terms of presentation, quailty and utility of the videos, that's getting superior by the day! Good luck to you!
Thank you Vijay. Very much appreciate that. Specially for your support during all the this time 🙏
Thank you Leila. You are great.
Just pure genius. Thanks.
This video came in exactly when I needed. Thank you
Really helpful and amazing , thank you very much
Thanks Laila. This video was extremely helpful.
Leila your videos are really good! Congrats! Thanks for sharing with us!
Really nice video and thoroughly enjoyed and learned from it.
Thanks
Love it super easy!!! Do you have classes?
We do. Check them out here: www.xelplus.com/
I know this is a little off topic, but for anyone removing duplicates in power query for excel or power bi, there is a trap. Yes, just like in an excel spreadsheet the first unique row found is retained while others are removed. However, the way that first row is defined is the trap. It is not defined as the first row as the data is currently, but the first row when the data was first loaded. So you might do a sort to ensure the rows you want retained are at the top then remove duplicates but this will not give you the correct result. The solution is to include Table.Buffer in the remove duplicate query to reset the state of the data to the current sort order. Then the power query remove duplicates operation will work the same as if you removed duplicates from an excel spreadsheet.
Thanks for this ☺️
I did not know this. thank you for sharing this info.
Excel also has a "remove duplicates" feature on the Data tab. No formulas needed except creating unique keys when needed.
@@MrSupernova111 You wouldn't use that if you were using power query though.
@@drew3030 . I use both depending on the complexity of the project. I don't like to limit my options.
superb video easy to understand and follow thank you
You explain very well and ur videos are informative. You covers maximum possibilities. Thank u.
Thank you so much, your videos are so informative.
Your videos are a big inspiration! just started out my own youtube (from my experience as a data analyst) All the best!
very good playlist, nice work, thanks a lot
Valuable video, very useful.
How to retain original value without colouring?
Wow! Thank you. It's very convenient and useful
Glad it was helpful!
YOU ARE PERFECT, AND THAT'S MORE THAN 1!
Very very helpful. Thanks a lot
Very useful..learnt something new... 👍
Thanks Leila for those tips!
You're so welcome!
Very Nice and practical.😀
This is very helpful 👌👌
Its a Discovery.. Thank You for the Valuable Information. I Have Been Using CountIf with start cell as Absolete and 2nd Cell as Relative in a Range.
A1:A100 Has Unique and Duplicate Values
Formula For B2 =CountIF($A$2:$A2,$A2)
Formula For B3 =CountIF($A$2:$A3,$A3)
Formula For B4 =CountIF($A$2:$A4,$A4)
Good thing with excel is that you never run out of things to do and make videos about
That's true!
Thanks for the free knowledge
Great video, very recommended!
Thank you very much! This is really helpful! It's amazing to see how many things we can do in excel 😃
Glad you like it!
You just answered my question from the short. Thank you!!!!
Excellent
❤
Thank Yu very much it helped alot at bureau of financial investigations.
Excellent 😊..Thank you 😊
Love how you casually added the emoji, but you just created a monster. I'm going to use an emoji in every formula from now on, just to make my day a little more enjoyable. Chances are, I'll be using the middle finger quite often.
Amezing mam.. superb
You are great!, I love your class!!
😭😭 Amazing as always. Thank you so much.
Great and really helpful and did my work
thanks for video
Very good animation and amazing explanation. Thanks for uploading such a useful content. Keep it up!
Searching for it yesterday and it's up there😀 Thanks 🤘
Thank for your great video.,very useful for me to use this. for this part 7:39 how to get only 1 duplicate
Super dear,thank's too much❤
As always, you made my live much easier! Thank you Leila ❤
Glad it was helpful!
Really helpful
Thank you 🙏
Really excellent
Thank you for sharing
My pleasure
It's always a treat to watch and learn from your videos. Just want to know how to create dummy data and query the data to apply formulas in different ways. If you can make a tutorial on same that would be great and I assume maximum viewers have similar concern while practicing ... thanks
If you want create dummy data for numberic =randbetween(100,200)
Thanks Leila for the video and for always keeping us abreast with excel advancements. Wondering if there's a formula for highlighting figures that 'knock off each other' in a column. Let's say 12 and -12 or -1,000 and 1,000? Thanks
Thank you!
Thank you so much. Amazing 👏
You are always awesome ma'm. Great tutorial 👍
Thanks Leila. Many of us deal with duplicate data on daily basis. This video is a boon.
Glad you think so!
😊Very Good 👌 Thank You for sharing
Great! Thank you
I love to use conditional formatting. Thats what I teach beginners at work
very very useful👍👍👍👍
Thank you Leila
I will try this later. Thank you!
Thank you Leila!!!!
Thank you Leila for this great video. Another way that I have to find duplicates is Pivot Table
Great tip Nelson! Now we have 5 ways.
Thank you Leila! This is very helpful for me 🙏🏾
In the last exercise to pull out just the duplicates, an alternative is to use UNIQUE instead of SORT in case you don't want to see the same record multiple times over.
This trick is very helpful in my work.
Thank you so much
Thanks 🙏🏽
This is amazing, this what I need right now, thank you sooo much😍
thank you very much
Thanks 🥳
sincere thanks
Fantastic. As always.
Great video!
Great, many many thanks
My pleasure!
Perfect 👍
Thanks!
You Rock Leila!
Thank you Leila for this nice video 📹 👍
You're welcome 😊
Very clever, thanks Leila!
Clever is one way of describing all of that
I needed this