Grab the file I used in the video from here 👉 pages.xelplus.com/find-duplicates-file

@jlyncanlas9846

8 ай бұрын

how about if you need to compare from two different excel files?

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!

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 😊

'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 ... 😍😍😍

@LeilaGharani

Жыл бұрын

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.

@LeilaGharani

Жыл бұрын

Thanks for the suggestion, John

Excellent guidance and this will help me so much in my work as I've to clean up ~21k rows of data. Thanks Leila.

Thanks Leila! exactly what Im looking for. Very timely. 3thumbs up!!!

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!

@LeilaGharani

Жыл бұрын

Thank you Vijay. Very much appreciate that. Specially for your support during all the this time 🙏

Good thing with excel is that you never run out of things to do and make videos about

@LeilaGharani

Жыл бұрын

That's true!

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.

@juliethramosrivera

Жыл бұрын

Thanks for this ☺️

@shylomathew

Жыл бұрын

I did not know this. thank you for sharing this info.

@MrSupernova111

Жыл бұрын

Excel also has a "remove duplicates" feature on the Data tab. No formulas needed except creating unique keys when needed.

@drew3030

Жыл бұрын

@@MrSupernova111 You wouldn't use that if you were using power query though.

@MrSupernova111

Жыл бұрын

@@drew3030 . I use both depending on the complexity of the project. I don't like to limit my options.

Just GORGEOUS. The lady and the formulas, Thanks for sharing once again. :)

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

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.

@LeilaGharani

Жыл бұрын

Glad you enjoyed it!

Super helpful! 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.

You explain very well and ur videos are informative. You covers maximum possibilities. Thank u.

Great video. This helped me after watching all other videos. A life saver!

Your videos are a big inspiration! just started out my own youtube (from my experience as a data analyst) All the best!

Leila your videos are really good! Congrats! Thanks for sharing with us!

Thank you Leila! This is very helpful for me 🙏🏾

This video came in exactly when I needed. Thank you

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)

As always, you made my live much easier! Thank you Leila ❤

@LeilaGharani

Жыл бұрын

Glad it was helpful!

That ConCat formula is a game changer. THANK YOU

Thanks Laila. This video was extremely helpful.

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

@trinitys.7833

3 ай бұрын

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!

We can simply use 'Countifs' instead of 'Countif' + Concatenate to integrate multiple columns while finding duplicates..Great video as always 👍

😭😭 Amazing as always. Thank you so much.

You just answered my question from the short. Thank you!!!!

I will try this later. Thank you!

Very good animation and amazing explanation. Thanks for uploading such a useful content. Keep it up!

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!!

@LeilaGharani

Жыл бұрын

Glad I could help!

Really nice video and thoroughly enjoyed and learned from it. Thanks

Thank you so much, your videos are so informative.

OMG you are ALWAYS the best!!! Thank you!!!

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.

Thanks Leila for those tips!

@LeilaGharani

2 ай бұрын

You're so welcome!

This is amazing, this what I need right now, thank you sooo much😍

Great! Thank you

Thank you Leila. You are great.

Just pure genius. Thanks.

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

@manjulakarthik2157

Жыл бұрын

If you want create dummy data for numberic =randbetween(100,200)

Good video! Your tips are a great way to keep me updated with excel. Thanks for sharing.

@LeilaGharani

Жыл бұрын

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

@LeilaGharani

Жыл бұрын

🙌

Fantastic. As always.

very good playlist, nice work, thanks a lot

Leila has saved my life 1000000000000000x thank you!

Great video, very recommended!

I needed this

Excellent and very helpful video. Can you recommend a method that allows a comparison of values, let’s say staff names, that are in 3 or more columns?

Thank you very much! This is really helpful! It's amazing to see how many things we can do in excel 😃

@LeilaGharani

6 ай бұрын

Glad you like it!

You are great!, I love your class!!

Excellent 😊..Thank you 😊

Thank you so much. Amazing 👏

I learned again...thanks

Thanks!

Very useful..learnt something new... 👍

I love to use conditional formatting. Thats what I teach beginners at work

Thanks for the free knowledge

Great and really helpful and did my work thanks for video

Thank you Leila!!!!

Really helpful

sincere thanks

You are always awesome ma'm. Great tutorial 👍

Wow! Thank you. It's very convenient and useful

@LeilaGharani

6 ай бұрын

Glad it was helpful!

Very Nice and practical.😀

Great video!

Thanks 🙏🏽

This is very helpful 👌👌

Thank you 🙏

😊Very Good 👌 Thank You for sharing

This trick is very helpful in my work.

Really excellent

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

Excellent ❤

I thought you will show how to do it without additional column. I created this solution: =COUNTA(FILTER($A$1:$B$7,($A$1:$A$7=A1)*($B$1:$B$7=B1)))>2 It creates range of duplicated values equal to current row. If there are more than 2 elements (assuming we have 2 columns) it means that there is at least one more duplicated row. I hope someone will find it helpful.

@viktorasgolubevas2386

Жыл бұрын

Agree. More or less known solution (a-la PBI style) is: =ROWS( FILTER( ... )) > 1

Thank you Leila for this great video. Another way that I have to find duplicates is Pivot Table

@LeilaGharani

Жыл бұрын

Great tip Nelson! Now we have 5 ways.

Thank you, Leila

Super dear,thank's too much❤

Perfect!

Thank you Leila

Valuable video, very useful.

@pvrajasekhar8368

Жыл бұрын

How to retain original value without colouring?

Perfect 👍

thank you and God bless :)

Nice work. This might be helpful. I used to get very odd behaviour using COUNTIF embedded in a lambda function that was called from within another LAMBDA (something to do with it not accepting the variable which although was a vector, was strictly not a RANGE, which it must take as its first argument). It did my head in! This formula avoids using COUNTIF, and IMO is more robust (when doing complex and nested lambdas) . Hope its useful COLUMN_DUPLICATES = LAMBDA(col_vector, LET( count, SCAN(0, col_vector, LAMBDA(a, r, SUM(--(r = col_vector)))), duplicates, IF( IFERROR(ROWS(UNIQUE(FILTER(col_vector, count > 1))), 0) = 0, "No Duplicates", UNIQUE(FILTER(col_vector, count > 1)) ), duplicates ) );

Wow!!!!!! Brilliant

Amazing stuff. Thank you, Leila! I have been trying to find a way to make a unique list of values across multiple columns (Let's say A has people who have bought product 1, B has people who bought product 2 and I want the full list of customers). Any ideas, guys? Do we have a better way to do it than copying one list, going to the end of the other and pasting it, then using unique?

Thank you for sharing

@LeilaGharani

Жыл бұрын

My pleasure

You Rock Leila!

Thanks 🥳

Thank you

YOU ARE PERFECT, AND THAT'S MORE THAN 1!

thanks a lot, u saved my life (Y)

Great, many many thanks

@LeilaGharani

Жыл бұрын

My pleasure!

Thanks

Thank for your great video.,very useful for me to use this. for this part 7:39 how to get only 1 duplicate

Searching for it yesterday and it's up there😀 Thanks 🤘

Wow brilliant