Excel - Conditional Formatting find duplicates on two worksheets by Chris Menard
Ойындар
Finding duplicates with Conditional Formatting in Excel is easy when the data is on the same worksheet.
When the information is on two worksheets, you have to write a formula and use the CountIF function.
chrismenardtraining.com
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ KZread keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!
Пікірлер: 236
I spent the last 2 hours trying this and replaying this video and thank god I have finally made it!!! I'm so glad that I found this video - i'm looking for duplicates between two spreadsheet that could that me days or even weeks to complete! This is just life-saving!!
Mr. Menard, you just saved me a full day of work. For that, I am thankful.
@ChrisMenardTraining
4 жыл бұрын
Daniel, glad to help.
@inekeabunawass5641
3 жыл бұрын
Me too. That was great!
Hi! I just wanted to say this is EXACTLY what I was looking for. I could not remember how to from school and needed the formula for work. Life saver :)
@ChrisMenardTraining
4 жыл бұрын
Glad it was helpful!
Thank you so much for this. I did have to watch the video twice and try the formula three times - but 10 minutes later you had saved me hours and hours of work. I really appreciate this video!
@ChrisMenardTraining
2 жыл бұрын
Glad to help. Thank you, Susan.
Thank you, Chris, it was just what I was looking for! And it saved me many hours of work checking them manually :)
@ChrisMenardTraining
3 жыл бұрын
Glad to help.
Thank you Chris! Exactly what I needed in a pinch. Lifesaver!
@ChrisMenardTraining
3 жыл бұрын
Glad it helped!
Thank You Chirs, You couldn't imagine how this tutorial helped me and saved my time.
Chris, thanks so much for sharing this. A true lifesaver for a project I was doing.
Wow. Yesterday I spent well over an hour searching for how to do this. I kept going down all these rabbit holes that led me to nowhere but frustration. Found this video today on my second try and in less than four minutes I’m a wizard! Thank you so much for this, Chris!
@ChrisMenardTraining
4 жыл бұрын
You're very welcome!
@nycollassilva3558
Жыл бұрын
@@ChrisMenardTraining sjscfq1clhfkv
I know this video is a couple years old, but it is still SO RELEVANT! I had two sheets with over 2000 rows of data and I needed to find like numbers by comparing both sheets. This formula WORKS!!! :) :) :) Thank you!!!
@ChrisMenardTraining
2 жыл бұрын
Great to hear! Thanks, Christina!
@aldenbayless7954
Ай бұрын
2 more years on top of your 2 years and still relevant!
Nicely explained! My lists were on two separate workbooks so I just had to copy one as a second worksheet on the first workbook and it worked out great. Thank you!
@ChrisMenardTraining
4 жыл бұрын
Glad it worked. Thank you.
This saved me so much time! Your instructions were clear and easy to follow. Thank you!
Thank you for your time and enjoy your Sunday also.
Thank you for this tutorial! This saved me a lot of time and energy at work today. Much appreciated!
@ChrisMenardTraining
3 жыл бұрын
You're very welcome!
This video is the best explanation of how to compare two sheets and find duplicates with highlighting formats, congrats!!!
This was way quicker than any other videos I've looked at . Thank you
I searched for hours before I found your video, thank you so much for making this so simple and clear.
Whenever I have an Excel problem I always search your channel. Appreciate the clear instructions. Miles better than any other instructions. Thanks Chris!
Excellent tutorial, Chris. Very practical and to the point. I had tried three others before finding yours. Thanks so much. Subscribed.
@ChrisMenardTraining
2 жыл бұрын
Thank you Judith.
Brilliant, just what I was looking for. Thank you so much!
@ChrisMenardTraining
4 жыл бұрын
Glad it was helpful!
Absolute life-saving trick.... Thank you so much, Chris.
@ChrisMenardTraining
3 жыл бұрын
You are welcome.
Awesome video. Easy to follow and exactly what I wanted. Thank you!
@ChrisMenardTraining
4 жыл бұрын
Vanessa, I appreciate the positive feedback. Have a beautiful day.
Thank you Chris! This video was really helpful!
@ChrisMenardTraining
4 жыл бұрын
Glad it was helpful!
Thanks so much for this video. I am an Executive Assistant and this saved me a lot of time on a task a client sent me.
@ChrisMenardTraining
Жыл бұрын
Glad to help. I do a lot of training for Executive Assistants through the American Society of Administrative Professionals. Thanks, Frances.
Very helpful and clear! Thanx very much!
Thanks for this tutorial. It was exactly what I needed.
@ChrisMenardTraining
4 жыл бұрын
You're welcome!
this was exactly what I was looking for! thanks!
@ChrisMenardTraining
4 жыл бұрын
Thank you for the positive feedback. Have a good weekend.
Amazing, clear and concise tutorial. Thank you!
@ChrisMenardTraining
2 жыл бұрын
You're very welcome!
Thanks for the video. . It's working with multiple criteria's as well using countifs
@ChrisMenardTraining
11 күн бұрын
Excellent!
Thank you so much for this video Chris!!
@ChrisMenardTraining
3 жыл бұрын
Glad to help.
Thank you, Chris. Very precise and to the point. I really Appreciate it!
@ChrisMenardTraining
Жыл бұрын
You are welcome.
Thank you so much! Easy and direct to the point.
@ChrisMenardTraining
3 жыл бұрын
You're welcome!
You are a life saver! Thank you for making this simple to follow video tutorial.
@ChrisMenardTraining
Жыл бұрын
You're welcome!
Thanks for posting this,very helpful!
@ChrisMenardTraining
4 жыл бұрын
Jason, I'm glad you liked it. Thank you for the positive feedback.
Perfect. Worked like a charm
That was exactly I wanted! So helpful, thank you.
@ChrisMenardTraining
Жыл бұрын
You're so welcome!
This is great, thanks, Chris
Thank you for this tutorial. Saved me hours of work.
@ChrisMenardTraining
3 жыл бұрын
Great to hear!
This worked for me. Thank you!
I've watched a few videos on this, wish I would have landed here first, thanks Chris!
@ChrisMenardTraining
2 жыл бұрын
No worries! You found me at least! :-)
This is really helpful. Thanks for sharing
@ChrisMenardTraining
4 жыл бұрын
You're so welcome!
Thank you so much. You saved me from a big one!!!!!!
@ChrisMenardTraining
4 ай бұрын
glad to help
God bless you man!
Thanks for sharing. Gonna give this a try on my project tomorrow
@ChrisMenardTraining
3 жыл бұрын
Thank you. Good luck on your project.
Excellent - thanks Chris!
@ChrisMenardTraining
2 жыл бұрын
My pleasure!
Tip top! Much appreciated Sir.
@ChrisMenardTraining
3 жыл бұрын
Glad to help. Thank you.
Many Thanks, Extremely thankful
@ChrisMenardTraining
5 жыл бұрын
Thank you.
Such a helpful video when dealing with thousands of rows. Thank you :-)
@ChrisMenardTraining
2 жыл бұрын
Thanks, Cath.
Appreciate this video so much, thank you Chris! Helped me out immensely
@ChrisMenardTraining
Жыл бұрын
Glad to hear it!
Exactly what I was needing.
@ChrisMenardTraining
4 жыл бұрын
Thank you.
Hi... How do you handle more than two worksheets? Is it the same process?
This is awesome! Thank you!
@ChrisMenardTraining
Жыл бұрын
You're very welcome!
Thanks for the idea. I was able to write my custom formula using this.
@ChrisMenardTraining
3 жыл бұрын
Glad it helped!
I have a workbook that I add sheets to each week. I need any duplicate data (range would be every existing worksheet in the book) to be apparent when entering the information in the new week's sheet. How do I do this? Is it possible?
you are a wizard sir thank you so much i was comparing inventory over several locatoins and i think this just saved me hours and hours of time
@ChrisMenardTraining
Жыл бұрын
Glad it helped
Very well done...thanks!
@ChrisMenardTraining
4 жыл бұрын
I appreciate it.
Thank you for posting this video.
@ChrisMenardTraining
3 жыл бұрын
You're welcome
Thank you! The only video I have followed and it worked! Super clear :)
To the point..excellent..Keep up the good work!!
@ChrisMenardTraining
4 жыл бұрын
Thanks, will do!
Very helpful! Thank you!
@ChrisMenardTraining
3 жыл бұрын
You're welcome!
This video saved me so much time and work. Thank you
@ChrisMenardTraining
2 ай бұрын
You're welcome!
Thank you! Perfect!
@ChrisMenardTraining
4 жыл бұрын
You're welcome!
This video was very helpful. Thank You!
@ChrisMenardTraining
Жыл бұрын
You're welcome!
Thank you so much for sharing. You just saved me several hours of work
@ChrisMenardTraining
Жыл бұрын
You are welcome!
Thank you so much this was a lifesaver
@ChrisMenardTraining
2 ай бұрын
You're welcome!
saving my time a lot thank you
@ChrisMenardTraining
2 ай бұрын
Welcome
Thank you!!!! You are a lif saver.
@ChrisMenardTraining
3 жыл бұрын
Happy to help!
Thank you. Very helpful
@ChrisMenardTraining
4 ай бұрын
You're welcome!
great Chris !
@ChrisMenardTraining
2 жыл бұрын
You are welcome.
Thank you so much sir. Your video easy to understand and not complicated.
@ChrisMenardTraining
Жыл бұрын
You are most welcome
realy cool trick chris.thanks a lot.
@ChrisMenardTraining
2 жыл бұрын
Glad you liked it!
Thanks, It is helpful.
Thank you, Its really helped❤❤
Hi Chris, if I wanted to do this but compare to maybe two columns using what would typically be the "and" function. How would I do this? For example, I want to know if the data in 'Column A - Sheet 1' matches any data in 'Column B - Sheet 2' AND 'Column D - Sheet 2.' I feel like this is really easy, but I cant figure out how to do it. I can figure out the "or" like if it matches in B or D to highlight but I want it to match both...not or. Kind of feel like I'm not making sense. Thanks in advance for the help! :) -C
This was great to find, just not exactly what I am after and I wonder if you have any idea to achieve what I need. I have 11 sheets in a file and want to copy and paste any duplicate data in 10 of the sheets to an 11th sheet. To check for duplicates, columns A, B & H are required, so if A, B & H match in more than 1 sheet, the data in that row is copied to the next available row in the 11th sheet. Is this achievable at all using COUNTIF or another function for that matter? Thanks so much
Chris, How do I look for values that are Non Duplicates on two different worksheets using similar countif formula?
Thank you sir!
@ChrisMenardTraining
9 ай бұрын
You are welcome!
Awesome, thank you so muchhhhh
@ChrisMenardTraining
4 жыл бұрын
You're welcome!
Thank you!
@ChrisMenardTraining
2 жыл бұрын
You're welcome!
What if I want the entire row highlighted.
Thanks this helped me so much!
@ChrisMenardTraining
3 жыл бұрын
Glad it helped!
@gwladysmorrissey4679
3 жыл бұрын
@@ChrisMenardTraining Is there a way to do this with near matches?
THANK YOU! Oh my goodness, running reports for work and i've spent the last 2 hrs trying to figure this out so I can eliminate extra daily work! This was so easy to follow!
@ChrisMenardTraining
4 жыл бұрын
Glad to help.
Thanks Chris
@ChrisMenardTraining
Жыл бұрын
You are welcome
This is what I was looking for, but for some reason it is only highlighting a few lines, it is not highlighting all of the duplicates, and I cant figure out why. I have manually copied a cell that I know is on both sheets, and used Find to confirm it is on both sheets, but it is not getting caught by the =countif formula. Re-did my data, used TRIM and CLEAN on it, and tried it again and it worked this time, so it looks like the first time I was trying it there was bad data mixed in.
tq sir...u do help me on this...as a beginner...tqsm
@ChrisMenardTraining
2 жыл бұрын
Most welcome!
Hello! Is there a way to highlight the entire row and not just the duplicate?
Greet contented and works as intended for me. One question @2:16 can you explain a little bit about why we added the "A2" and called it relative reference. i'm just trying to understand its purpose.
@momiley6475
2 жыл бұрын
Well i think thats the cell the data started on both sheets
Thankx Chris
@ChrisMenardTraining
3 жыл бұрын
You're welcome.
This was a wonderful video, thank youfor it. is there a way to have it delete the duplicate instead of highlighting it?
Thanks for the video, how about if it's from two different excel files? Without copying/pasting the info into one workbook?
Hi, it was exactly what I was looking for, but I've got a problem. After doing the steps, in the end, I have received an error that says "you can not use references to other worksheets or workbooks for conditional formatting criteria." could you please help me with that? thanks in advance.
Thanks a lot sir for making such a good video it is really help
@ChrisMenardTraining
Жыл бұрын
Most welcome
Thankyouuu very much sir
@ChrisMenardTraining
3 ай бұрын
Most welcome
Thank you sir
@ChrisMenardTraining
9 ай бұрын
Welcome
Thanks!
@ChrisMenardTraining
2 жыл бұрын
Thank you. Much appreciated.
Thank you.
@ChrisMenardTraining
2 жыл бұрын
You're welcome!
Worked for me❤
@ChrisMenardTraining
6 ай бұрын
Good to know. Thank you.
I Love You thanks alot
@ChrisMenardTraining
Жыл бұрын
Glad to help.
how do we check duplicates on multiple worksheet(more than 2)?
Thank you
@ChrisMenardTraining
9 ай бұрын
You're welcome