Compare Two Sheets for Duplicates with Conditional Formatting
Vložit
- čas přidán 7. 08. 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!
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
This was great. Thank you!
The explanation about the relative reference in Conditional Formatting was very useful.
Thank you so much... just spent about an hour trying to find how to do this. Your instructions are clear and easy to follow.
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.
Great Tutorial And Explanation For Duplicates Using Conditional Formatting...Thank You Jon :)
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 your detailed explanation! You’ve made it very easy to understand :)
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.
Hi Jon. Great tips! You always make it look so easy :)) Thanks of sharing and Thumbs up!!
Another great one Jon. Thank you.
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!
Thank you man, i was searching a lot for this formula ❤️
First-rate exposition. Thanks Jon.
Great tutorial John. Thanks a lot
This was fantastic! Thank you so much!
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 :)
Thanks a lot, Sir! You did it well.
Very helpful tutorial - thank you!
Another great tutorial...thanks!
Great tip. Thank you!
Thanks, exactly what I needed!
Wow! Thank you! SOOO helpful. You just saved me so much time.
You're welcome, Gabbie! 😀
thanks man found soultion of my error in just under 3 mins of your video
Awesome! I'm happy to hear it. Thanks Jay! 🙂
Brilliant you are a great teacher
thank you so much. You are very clear
Learnt Something new and practical kudos to you sir
Thank you it was really helpful 😊
Thanks for your amazing tutorial video.
You are welcome, @user-jp3qb7fq5l 😀
The best person .that who help another person
Good and clear explanation
Thank you for this video
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!
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!
Thank you very much, worked very well here
You're welcome, Otávio! :)
Worked Great, thank you SO much.
Glad it helped. 😀
Hi! This was informative.
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 so much!!!!!!
Helped me loads thank you !
Glad it helped! 😀
Can we highlight duplicates in multiple sheets within a workbook in google sheets
Awesome video 👌🏼👌🏼
Thank you!
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?
Helped me a lot! Thank you! :)
You're welcome 😀
Thanks for this useful info. How do you configure this if you have a column of extension on 3 worksheets?
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?
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
Great Video, but how to omit the blank cells from the conditional formatting?
Great video. How do you use this formula when you have more than 2 worksheets? I usually have 5 or more.
Awesome
Good information bro tq. I am from india.
Thanks, Nukala! :)
2:01 When I click F4 there, for some reason I don't get the $ symbols
Can you also explain how to highlight duplicate entries in both sheets.
how to press F4 on Mac!? it shows spotlight search!
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.
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
On a Mac, the F4 doesnt have the same result. Where do you get that from the menu system?
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?
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 !
do you have a video that explains how to do this in google sheet?
Thanks.
Does this method work for all software versions from 2016 up to 365? or this method is for older versions. Please clarify.
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
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
Thank you for demonstration. However, I've tried the same with names and this solution did not work
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.
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"
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
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?
Does this have to be in a table format ?
How can we do this using two different workbooks?
i love you
Sir how to do this in more than two sheets?
Does this work with google spreadsheets with the same formula?
Very simple but I could not make it work, it keeps sending me to update value...
It should be noted that the order of the two rules makes a difference.
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.
so what happens if your data sets are different sizes?
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.
Hello, if i have 5 sheets, how can i do,Thanks
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
I dont have a keyboard button that says "End"
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 CZcams videos on it.
this can't be done on google sheet?
Im triing to follow this formula with 10 sheets and it doesnt work
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.