Compare Two Sheets for Duplicates with Conditional Formatting
HTML-код
- Опубликовано: 28 июн 2024
- Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
In this video, I demonstrate how to compare two worksheets and use conditional formatting to highlight entries that are found in both. Cells that have multiple duplicates can be formatted differently to stand out.
If you’d like to read the accompanying blog post on my website, you can access it here: www.excelcampus.com/tips/comp...
Related Videos:
Conditional Formatting Rows Based on Another Cell Value: • Conditional Formatting...
Conditional Formatting for a List of Partial Matches in Excel:
• Conditional Formatting...
Highlight Rows Between Two Dates with Conditional Formatting in Excel: • Highlight Rows Between...
Apply Conditional Formatting to Shapes in Excel: • How To Apply Condition...
#MsExcel #ExcelCampus
00:00 Introduction
01:08 Conditional Formatting
03:05 Apply Conditional Formatting
04:48 Important Notes
I appreciate how you explain everything in a simple way, making it very straightforward!
Your delivery is precise, concise and very easy to follow. Gret work John!
Great Tutorial And Explanation For Duplicates Using Conditional Formatting...Thank You Jon :)
Hey dude, yesterday, it took me a bit to get the results, probably because I was stressed out and in a rush but, today in the morning, Saturday, relaxed and after a cup of coffee I was able to do what I wanted to do. Get duplicated IDs from a list of 19,332 users. In a couple of minutes, I finally was able to accomplish my goal.. thank you so much! you got my subscription for more, the review, and the like... good explanation, calmed, good tone, friendly, and straight to the point.. thanks
Thank you so much... just spent about an hour trying to find how to do this. Your instructions are clear and easy to follow.
This was great. Thank you!
The explanation about the relative reference in Conditional Formatting was very useful.
I've already finished 15 different videos, this one really helps me the most! thank you mister
Same here, tedious process...Outlook 365/Excel should just make an icon button for this method = Simple!
Thank you for the clear, concise video. I was able to apply your instructions to my sheet. I appreciate you taking the time to help others.
Thanks Jon! You just helped me create a really important report for work with this easy to follow video!
This was extreemely clear and useful! Thank you!
Awesome explanation professor. Always clear to understand.
Thank you for your detailed explanation! You’ve made it very easy to understand :)
Another great one Jon. Thank you.
Thank you man, i was searching a lot for this formula ❤️
Thank you so much! This was incredibly helpful. You must be a teacher. I know nothing about formulas and didn't really understand the "True" and "false" and some other references you made, but I was successful in comparing my 2 sheets!
First-rate exposition. Thanks Jon.
Great tutorial John. Thanks a lot
Hi Jon. Great tips! You always make it look so easy :)) Thanks of sharing and Thumbs up!!
This was fantastic! Thank you so much!
Another great tutorial...thanks!
Worked Great, thank you SO much.
Glad it helped. 😀
Very helpful tutorial - thank you!
Great tip. Thank you!
Thanks, exactly what I needed!
thanks man found soultion of my error in just under 3 mins of your video
Awesome! I'm happy to hear it. Thanks Jay! 🙂
The best person .that who help another person
Thanks a lot, Sir! You did it well.
Wow! Thank you! SOOO helpful. You just saved me so much time.
You're welcome, Gabbie! 😀
Can we highlight duplicates in multiple sheets within a workbook in google sheets
Good and clear explanation
Thank you it was really helpful 😊
Brilliant you are a great teacher
Learnt Something new and practical kudos to you sir
thank you so much. You are very clear
Helped me loads thank you !
Glad it helped! 😀
I found your channel for the first time because you showed me how to do a macro to have the pivot table refresh now I'm looking for a way to macro for the pivot to select all dates (new dates) added to the data. Thanks 😀
Hi, Have you tried having a dynamic range to your pivot table? It is possible with an offset function. This way any time your source data is changed (added/deleted rows) the range increases dynamically and the new data is added to your pivottable :)
Hey Jon. Thanks a lot for this! This really saved me! Sometimes it highlights values and when I try to find them with ctrl + F in the other sheet, it really isn't there...would you know why that would happen? Thanks again!
Awesome video 👌🏼👌🏼
Hi, thank you for this very insightful video tutorial! I have a question: Is there a way to Format the data that the rule applies to so that it returns Icons instead of just a color format, and if so how can that be done ? Thank you!
Hi! This was informative.
Thank you for this video
Thanks for your amazing tutorial video.
You are welcome, @user-jp3qb7fq5l 😀
Thank you very much, worked very well here
You're welcome, Otávio! :)
You are amazing!!!tysm!
This was great! Thank you. I do have a question, if I update info in the range it's searching, should the file color up?
Thanks for this useful info. How do you configure this if you have a column of extension on 3 worksheets?
Thank you so much!!!!!!
Thank you very much.. this video was very helpful..
Glad it was helpful, @chishachatupa8785 ! 😀
I like your soothing voice and great explanation's. Can you do this using Power Query? Many thanks
Thank you!
Helped me a lot! Thank you! :)
You're welcome 😀
Great Video, but how to omit the blank cells from the conditional formatting?
Perfect !
Thankyou !!!
Thanks for the video Jon. I have been using conditional formatting for many years but have always had problem with the 'applies to' on Rules Manager changing when I move, add, or delete records within the sheet. I have a monthly running spreadsheet with conditional formatting applied to several different fields, but as I move, add, or delete records the conditional formatting breaks on the 'Applies to' in Rules Manager.
Is there any way to always apply the conditional formatting to the complete field when move, add, or delete a record?
Can you also explain how to highlight duplicate entries in both sheets.
Great video. How do you use this formula when you have more than 2 worksheets? I usually have 5 or more.
Good information bro tq. I am from india.
Thanks, Nukala! :)
Awesome
Hi, first of all congratulations for the channel, always very useful and well done.
I have a sequence of data in columns:
name
surname
blank
name
surname
blank
and I need to transpose them on single lines in the form:
name [blank] surname
You would have some advice for me.
Thanks for the time you want to dedicate to me
2:01 When I click F4 there, for some reason I don't get the $ symbols
I tried the same as shown in the video to display duplicate counts but it's showing for only 1 row, rest all is empty
Thank you for the awesome video! However when I tried the same "countif(range,criteria)" equation it only creates a value at the top of the chart where I inputted the equation in one cell, instead of creating a whole column of values. Do you know why this happens? Thanks!
same
Very helpful video. However, I can definitely see how newbies would get thrown off if they're new to Excel and don't know how to create a table.
how to press F4 on Mac!? it shows spotlight search!
Hi Jon,
Thanks again for a Very Nice and relevant Video !
One query !
Is there any way to maintain the sanctity of the Conditional Formatting Range when deleting or adding rows or extending the range ?
Can we use defined named ranges within conditional formatting to achieve the purpose ?
Can we use table ranges within conditional formatting to achieve the purpose ?
Any other idea ?
Kanwaljit
This is a pretty old comment on an old video so probably not getting answered. My experience is yes, if the formula includes the table columns rather than a specific range the formatting seems to handle when I add and delete rows or filters to the data.
@@Trisjack20 Thanks Dear ! I will try it !
On a Mac, the F4 doesnt have the same result. Where do you get that from the menu system?
do you have a video that explains how to do this in google sheet?
Thanks.
does not work. i get an error when trying to create the new rule. this is what it says::::: there is a problem with this formula. not trying to type a formula? when the first character is an equal or minues, excel thinks its a fomula. you type: =1+1, cell shows 2. to get around this, type an apostrophe first: you type: '=1+1, cell shows =1+1. i can only press ok and it takes me back to fix the issue. it will not let me move forward. btw..........the formula works for it to show 1s and 0s. but not to do the color between the sheets.
What if the duplicate isn’t entered till later? How can I have an entire column only highlight the cell once the duplicate is finally entered which may be a few weeks difference
Sir how to do this in more than two sheets?
Thank you for demonstration. However, I've tried the same with names and this solution did not work
Does this method work for all software versions from 2016 up to 365? or this method is for older versions. Please clarify.
so what happens if your data sets are different sizes?
How can i apply this for rows I am having two different sheets. I need to find which identical rows from one sheet are exist in another sheet
Any fixes? The countif formula is working but only on the first cell where I typed the formula. It's not producing a return for all of the cells in the spreadsheet. Any ideas on what I could be doing wrong?
How can we do this using two different workbooks?
i love you
Does this have to be in a table format ?
Hey there jon,need your help here, I am successfully able to do the function part
After I go and click copy, and then try to select the range the cells turn into a paste window.... I am not able to copy the formula to the conditional formating piece... Again if I try doing it manually
It gives me an "Error : you cannot use references to other worksheets or workbooks for conditional formatting criteria"
I am dumb. If in the formula, you are referring to the cell Current!B2, why there are highlighted values later from columns C and D? I don´t understand it :S
It should be noted that the order of the two rules makes a difference.
Does this work with google spreadsheets with the same formula?
Dear sir, i want to compare three sheets then what is the formula?
I wondering too. Caz mine need to compare more than 10 sheet, and the id cannot be duplicate. Not sure how to do it.
Very simple but I could not make it work, it keeps sending me to update value...
It not working for me on office 365. Some data will be missing without being highlighted even if they are repeated. Is there any problem with excel or version of it?
I dont have a keyboard button that says "End"
there seems to be a bug with the function which I have been fighting with for some time. at 4:40 we see rows 64 and 65 have been highlighted but the actual function returns 0.
do you have a fix for this?
I noticed the same issue. John - please advise.
I need a formula to calculate how many holidays comes on Sundays in a month, please reply
Sounds like you need the formula Networkdays. There are a bunch of RUclips videos on it.
Hello, if i have 5 sheets, how can i do,Thanks
this can't be done on google sheet?
Im triing to follow this formula with 10 sheets and it doesnt work
Hi, in my excel workbook I have 200+ sheets & sheet names defined.
Every sheet contains data like student name father name address and mob no.s
if I enter sheet name I should get all the data in that particular sheet contains. If anyone knows kindly advice
INDIRECT function will help you create the formula you are looking for. Another way is to use powerquery and merge all the data into one
hi hussain sir have i am also facing same problem. have u got the solution? please tell to me
How can i find the dublicate values for more than two sheets
Great question, Augestin! 😊 Extend the COUNTIF formula in Cell E2 with additional COUNTIF ranges. For example, if there were four sheets, the formula would look like the one below.
=COUNTIF(Historical!$B$2:$F$1001,Current!B2)+COUNTIF([third sheet range],Current!B2)+COUNTIF([fourth sheet range],Current!B2)
That formula would add the results of the matches on the other three sheets. Hope that helps! 🙂
@@ExcelCampus Thanks sir
Hi, i get the feedback that this is not a formula - I think it has to do with the criteria (?) Do you see my mistake here? I am very lost =COUNTIF(Pipedrive!S2:S9298:D365!K2:K3252), 'D365'!K2) =COUNTIF(Pipedrive!S2:S9298:D365!K2:K3252, 'D365'!K2) is not working either.