Compare Two Sheets for Duplicates with Conditional Formatting

[@acho1975]

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

[@SalikKhatri]

Your delivery is precise, concise and very easy to follow. Gret work John!

[@crissavillarosa3568]

I've already finished 15 different videos, this one really helps me the most! thank you mister

[@melissalang78]

Same here, tedious process...Outlook 365/Excel should just make an icon button for this method = Simple!

[@DavenportDecals]

Thank you so much... just spent about an hour trying to find how to do this. Your instructions are clear and easy to follow.

[@liene4619]

I appreciate how you explain everything in a simple way, making it very straightforward!

[@bobdunyak9869]

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.

[@darrylmorgan]

Great Tutorial And Explanation For Duplicates Using Conditional Formatting...Thank You Jon :)

[@niafporter]

Thanks Jon! You just helped me create a really important report for work with this easy to follow video!

[@sepantaam]

thank you man, you save my time!

[@petecardona8203]

Awesome explanation professor. Always clear to understand.

[@mrmusicloverable]

Another great one Jon. Thank you.

[@mohamedmarzouk5305]

Thank you man, i was searching a lot for this formula ❤️

[@AnthonyHarris-fe6zo]

Very helpful tutorial - thank you!

[@blessedmpombo7351]

Good and clear explanation

[@AbdifatahHassan-n9p]

Before more Vedio, final i find this great opportunity thanks 🎉🎉🎉❤

[@salmaalaa6158]

The best person .that who help another person

[@catherinevega1456]

This was extreemely clear and useful! Thank you!

[@reshmasaldanha5087]

Hi! This was informative.

[@christinet5074]

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!

[@nadermounir8228]

Great tutorial John. Thanks a lot

[@Gabbieesque]

Wow! Thank you! SOOO helpful. You just saved me so much time.

[@ExcelCampus]

You're welcome, Gabbie! 😀

[@MrFordyy]

Thanks, exactly what I needed!

[@mandykennedy4423]

Brilliant you are a great teacher

[@KM-co5mx]

This was great. Thank you! The explanation about the relative reference in Conditional Formatting was very useful.

[@jaychauhan360]

thanks man found soultion of my error in just under 3 mins of your video

[@ExcelCampus]

Awesome! I'm happy to hear it. Thanks Jay! 🙂

[@shwetakolli7809]

Thank you it was really helpful 😊

[@Princess_Slaya]

Thank you for your detailed explanation! You’ve made it very easy to understand :)

[@ziggle314]

First-rate exposition. Thanks Jon.

[@EnaanFarhan-k6d]

Thanks for your amazing tutorial video.

[@ExcelCampus]

You are welcome, @user-jp3qb7fq5l 😀

[@meenalnafde6216]

Awesome video 👌🏼👌🏼

[@chishachatupa8785]

Thank you very much.. this video was very helpful..

[@ExcelCampus]

Glad it was helpful, @chishachatupa8785 ! 😀

[@faroukhpoacha1]

Learnt Something new and practical kudos to you sir

[@domusbr]

Thank you very much, worked very well here

[@ExcelCampus]

You're welcome, Otávio! :)

[@carolynweaver5569]

This was fantastic! Thank you so much!

[@wayneedmondson1065]

Hi Jon. Great tips! You always make it look so easy :)) Thanks of sharing and Thumbs up!!

[@RobertAfrick]

Another great tutorial...thanks!

[@zavaidok]

Helped me a lot! Thank you! :)

[@ExcelCampus]

You're welcome 😀

[@chriswebb7961]

Thanks for this useful info. How do you configure this if you have a column of extension on 3 worksheets?

[@karladuenas1482]

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 😀

[@skulz66]

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 :)

[@ManuelPerez-xu6lm]

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?

[@omprakashojha9698]

Great tip. Thank you!

[@jonnieyeshaw5731]

Thanks a lot, Sir! You did it well.

[@Essays4College]

2:01 When I click F4 there, for some reason I don't get the $ symbols

[@mdivya9747]

Can we highlight duplicates in multiple sheets within a workbook in google sheets

[@gordoncorlette2730]

Worked Great, thank you SO much.

[@ExcelCampus]

Glad it helped. 😀

[@juanfelipecaicedo7035]

thank you so much. You are very clear

[@nukalasathish9926]

Good information bro tq. I am from india.

[@ExcelCampus]

Thanks, Nukala! :)

[@DonBrown-yy2uw]

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?

[@adamlevy2285]

Helped me loads thank you !

[@ExcelCampus]

Glad it helped! 😀

[@SonidosdelAlma369]

You are amazing!!!tysm!

[@carlosmarquez7717]

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!

[@sierrabergsgaard5314]

same

[@Theodore_e]

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!

[@jace3885]

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!

[@naveenaugustine8704]

Can you also explain how to highlight duplicate entries in both sheets.

[@khalidhaariye950]

I like your soothing voice and great explanation's. Can you do this using Power Query? Many thanks

[@ANAS577]

how to press F4 on Mac!? it shows spotlight search!

[@CideeStudio]

Thank you for this video

[@hibaassi9461]

Great Video, but how to omit the blank cells from the conditional formatting?

[@francosepulcri4283]

do you have a video that explains how to do this in google sheet? Thanks.

[@charlie0801]

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?

[@jjensen3128]

I noticed the same issue. John - please advise.

[@Unique__Queen__]

Thank you so much!!!!!!

[@jamieholley8204]

Great video. How do you use this formula when you have more than 2 worksheets? I usually have 5 or more.

[@ArunKumar-wz3dc]

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

[@lovecareshare]

Thank you!

[@dominicconrad9010]

On a Mac, the F4 doesnt have the same result. Where do you get that from the menu system?

[@tle6077]

Does this have to be in a table format ?

[@pawanchowdary9230]

Dear sir, i want to compare three sheets then what is the formula?

[@micheleice3903]

I wondering too. Caz mine need to compare more than 10 sheet, and the id cannot be duplicate. Not sure how to do it.

[@tutsecret499]

Does this method work for all software versions from 2016 up to 365? or this method is for older versions. Please clarify.

[@tedcasa3422]

Does this work with google spreadsheets with the same formula?

[@TheLogicLives]

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?

[@jayt.4026]

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.

[@Essays4College]

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.

[@augestindermit4133]

How can i find the dublicate values for more than two sheets

[@ExcelCampus]

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

[@augestindermit4133]

@@ExcelCampus Thanks sir

[@friend1439]

Sir how to do this in more than two sheets?

[@Knowyourworld05]

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

[@abte3033]

Hello, if i have 5 sheets, how can i do,Thanks

[@scotthughes5056]

Thankyou !!!

[@km-ali5982]

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?

[@nitrodistribution]

Thank you for demonstration. However, I've tried the same with names and this solution did not work

[@elizabethdowney3908]

How can we do this using two different workbooks?

[@faroukhpoacha1]

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"

[@destiny3403]

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

[@rasmia09]

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

[@ethanedwards6725]

so what happens if your data sets are different sizes?

[@cnsdaily7154]

I need a formula to calculate how many holidays comes on Sundays in a month, please reply

[@dejabluek8297]

Sounds like you need the formula Networkdays. There are a bunch of KZbin videos on it.

[@firaskafaween8475]

Perfect !

[@ANAS577]

I dont have a keyboard button that says "End"

[@ItzAsgharHussain]

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

[@skulz66]

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

[@pawanchowdary9230]

hi hussain sir have i am also facing same problem. have u got the solution? please tell to me

[@ulludacharkha]

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

[@Trisjack20]

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.

[@ulludacharkha]

@@Trisjack20 Thanks Dear ! I will try it !

[@vittoriacatteneo7385]

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

[@juanlara5613]

Very simple but I could not make it work, it keeps sending me to update value...

[@37777steve]

It should be noted that the order of the two rules makes a difference.

[@agnes8738]

this can't be done on google sheet?

[@vandr_]

Im triing to follow this formula with 10 sheets and it doesnt work

[@charmainecarby4274]

Awesome

[@tokinagata8195]

i love you

[@Stephanie-bd5ti]

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.