If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
Hi This is a great tutorial - i have two sheets of data listing phone numbers for staff - each month staff join and leave and I want to find the difference between the two sheets - however the lines don't always correspond as a name in line 21 in one sheet is there in second sheet but at postion 14 (for example) is there a way I can just find the new or different values in the second sheet?
Hi TrumpExcel, I am working in Aditya Birla Group in Dubai for past 4 years in Field of Excel Automation. And i must appreciate you for the way you are explaining to the learners. I usually do not see the basic videos but your thumbnail was too attractive to refuse. Kudos to Excel. Also in case you or any of your excel enthusiastic friend want to join for some role ill be happy to help. From Mudit Lalwani
You are teaching this so perfectly. This is so much easier and faster way to reconcile 2 data sets among many other methods sown in other videos . Thanks a Ton
Thank you first for your nice videos and explaining things in Excel. In your video "How to Compare Two Excel Sheets (and find the differences)" you are talking of two identical lists and your formula only works with identical sheets. Here my question: Is there also a similar formula when the lists are not identical? (maybe with vlookup, match & index). Thank you for your answer, Danny
Thanks for the video. I have one suggestion for improvement: Earlier I also used in conditional formatting the simple formula you suggest, but this has disadvantage that when you manipulate with sheet cells (eg. add/delete rows or columns), references break down and it doesn't work anymore (formatting must be restored). Much better solution is to use the formula: =INDEX(Sheet1Area,ROW(),COLUMN()) = INDEX(Sheet2Area, ROW(), COLUMN()) This will work no matter what cell manipulations you do in sheets in the future.
For lazy people, =if(cell1!A1cell2!A1"cell1 Value:"&cell1!A1&Char(1000)&"cell2 value:"&cell2!A1,"") Thank you for the wonderful ppt, keep up the good work.
Great tutorial - Q - How to get the delta between two Tables - using PQ - example: Need to know using an SW assets inventory Table, a client that has not been installed using the client current comparison Table. I must show the delta only using the Asset Name as the link field.
Thank you for the video but I want to know how will you know if data in one worksheet is present in another worksheet. Since in this video your working on the difference between two cells. Example. Let's say we have the figure 10 in cell A2 and 10 is present in another cell, let's say A4 in another worksheet. How will you know that the figure is present in that cell. Looking forward to your reply. Thanks
Good video, do you have a formula that will compare an entire workbook of 12 pages to a separate worksheet with numbers and names? I need the ones that match to be highlighted.
Thanks for the tips! Is this only for the comparison between the data in the same row from two workbooks/sheets, i.e. Row 1 to Row 1 and etc.? I have two different workbooks - Workbook A and Workbook B. Workbook A is a full list of a set of data that I exported. One of the columns in Workbook A contains identical data as one of the columns in Workbook B, but they are all in different rows. Is there any excel formula that we can use when we are comparing data in different rows?
Hi Shannon, did you get a reply back from Sumit Bansal? If not did you figure it out? I would be interested in knowing how to compare 2 files successfully. Thanks.
I am facing the issue of for highlighting the changes You cant use references to other worksheets or workbooks for Conditional formatting criteria . Please help
@@av7829 that function is used to put the text in another row but in the same cell, that's why you can see the Feb values in another line or row below the Jan values.
Great video, thanks. I have 2 sheets (previous and current month reporting) each have an unique ID field and I want to compare the dates to find if any dates have changed. The current month will have new rows of data and some of the previous month data (completed items) will be removed. Therefore the data sets are not matching using a filter on the unique ID. Any advice?
I too am curious. I have a similar issue but with tracking incoming and departing employees Edit: so I found that using IF(ISNA(VLOOKUP helped me. I can't post snips of my sheet but you can reverse engineer my formula below: IF(ISNA(VLOOKUP(B62,[ARRAY NAME],1,0)),"REMOVED","NO CHANGE") Don't forget to change the cell to whatever it is in your sheet
From ekm: E-verification, Drill bits, painting brush,submersible heater,chisel,chintheru, Mara ചുറ്റിക, projector motor and transformer. Old fan motor, cheriya vaakkathi, long drill bit, nylon, uli get ready, Rajesh license, അറക്ക പൊടി
Thanks, Sir. I am not able to compare two excel sheets. Error is showing as : "You cannot use references to other worksheets or workbooks for conditional formatting criteria" Sir, please give solution.
What store were those changes in? What is the change and what is the store number the changes relate to? Could take this video and take the next step in relating the store number? Thanks
a scenario where store is in a different order or may not be in incremental order then how do we compare two spread sheets considering store as the primary key.
Hi sumeet your all video are very good to update my knowledge in excel. I have following query please update * I have some customer data in different-2 sheets where column heading is same. how i can merge data and keep them in one sheet. Example: Sumit Bansal is my customer. In one sheet Sumit Bansal and his company name is present and in another sheet his contact no.and in 3rd sheet his email id and website address is present. kindly advise how to merge all three sheets.
I have an employee headcount of 2020 extracted from an HR platform and a new employee headcount of 2021 from a new HR platform. Both can be formatted as xlxs or csv. I need to find out what employees show on the 2020 list that we do not have on the 2021. Those employees for 2020 need to be added to the 2021 headcount list. How do I do that?
I have a different problem but do need comparison. I have 2 Excel files. File A is a list of all of our customers, total 2000 lines. File B is a list of all of our customers who purchased from us in 2019, total 1675 lines. The customer numbers are all in column A. so i just need to compare column A on both Excel file, highlight the customer # on File A that are not on File B. so i can eliminate our Dead customers on File A. Any idea? Thanks
Agree, most of the time when I am creating videos, I tend to use small dataset as these are easier to demo. For large data, have you checked Power Query. It's amazing and can handle large dataset with ease. Again, depends on what you are trying to achieve
I tried and I get "FLASE" for all .. trimmed just to two columns to replicate exact this as I was excited that we can give table's first cell to compare with another table's cell and can be dragged .. Immensely useful, if I get this working..For me the formula is working when I point to the column of comparison in this case B1 ( with Headers).
Does it work for content matching of two webpages? I have long text from two sites I need to compare and find only the mismatched word/words and print it. Can we do that or it will return the entire text even if there is a single word mismatch and rest all is matching?
@Trump Excel I followed along your instruction and enjoyed every minute of it. Then I got stuck where the need to be able to highlight differences in data when comparing both sheets (in the same workbook). A pop-up message appeared "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria". I am using Excel 2007. Please help! Thank you in advance for sharing with us this valuable tips on using Excel. I have Like and Subs to your channel :)
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what topics you want me to cover in future videos.
Hello All , what if i need to compare more then 2 months could you please till me how can i do it
Hi This is a great tutorial - i have two sheets of data listing phone numbers for staff - each month staff join and leave and I want to find the difference between the two sheets - however the lines don't always correspond as a name in line 21 in one sheet is there in second sheet but at postion 14 (for example) is there a way I can just find the new or different values in the second sheet?
This is super useful. Your English is super cool too. Thank you
@@paulinelawrence3827 I'm having the same problem
Add the formula to annotate the store number please.
THE 3 G man Gentle, Genuine, Genius...
Respect, from Ethiopia
The last part is totally awesome. Thank you sir!
One of the most useful formula, I was searching to find the difference and get in one single sheet! Thanks !
The way of explanation is too good thanks for video..
you know how to teach.
thank you very much and greetings from Brazil.
Thank you for this clear, easy-to-follow instruction.
Hi TrumpExcel,
I am working in Aditya Birla Group in Dubai for past 4 years in Field of Excel Automation.
And i must appreciate you for the way you are explaining to the learners.
I usually do not see the basic videos but your thumbnail was too attractive to refuse.
Kudos to Excel.
Also in case you or any of your excel enthusiastic friend want to join for some role ill be happy to help.
From
Mudit Lalwani
For complicated and high volume reconciliations with multiple files, I have been using Foxpro programs since a long time....
You are teaching this so perfectly. This is so much easier and faster way to reconcile 2 data sets among many other methods sown in other videos . Thanks a Ton
No, there is way too much unnecessary information.
This video was awesome!!! thanks!! looking forward to new tips!
Excellent , Easy to understand, Please keep it up
You are an awesome faculty, explain things step by step...
I thought it was going to be a different video and STILL found it useful.
Exactly what I wanted to know how to do! Excellent explanation. Thank you. 😊
Glad you found the video helpful 🙂
Thank you first for your nice videos and explaining things in Excel.
In your video "How to Compare Two Excel Sheets (and find the differences)" you are talking of two identical lists and your formula only works with identical sheets. Here my question: Is there also a similar formula when the lists are not identical? (maybe with vlookup, match & index). Thank you for your answer, Danny
You are very good in explaining..very helpful.
from few days i am looking for this kind of video to get solution. finally found. thanks.
This is great. This is going to save me tons of time. Thank you.
Undoubtedly this is extremely helpful, keep up the good work Man
Thanks you sumit, for covering such a daily occurring problem.
Very useful video 👍👌👏
Glad you found it useful!
Thanks for the video. I have one suggestion for improvement:
Earlier I also used in conditional formatting the simple formula you suggest, but this has disadvantage that when you manipulate with sheet cells (eg. add/delete rows or columns), references break down and it doesn't work anymore (formatting must be restored).
Much better solution is to use the formula: =INDEX(Sheet1Area,ROW(),COLUMN()) = INDEX(Sheet2Area, ROW(), COLUMN())
This will work no matter what cell manipulations you do in sheets in the future.
Please give a video of this topic.
I faced the issue you mentioned.
It would be great if you can make a tutorial.
@@chowdhurysunzidmahbub79 can check this one.
ua-cam.com/video/5fsmy3uwoc8/v-deo.html
Very good and very very useful..Thanks for posting..Loved it
These were valuable tips. Thank you so much.
Love to learn your tutorials Sumit. Very much useful...🙏
Thanks Sujith... Glad you're finding the videos useful!
Thank you first for your nice videos and explaining things in Excel.
Its very useful. Well explained. Thank you
solved my whole office problem 👍👍👍
I was looking for this formula for long time.
For lazy people,
=if(cell1!A1cell2!A1"cell1 Value:"&cell1!A1&Char(1000)&"cell2 value:"&cell2!A1,"")
Thank you for the wonderful ppt, keep up the good work.
Dear Sumit, excellent video, thank you very much!
Asslam Alaikum
Sir I am a subscriber of your channel and your excel videos are very useful....
Very nicely explained 👍. Very useful for day to day working
Sirji Tusi great ho this was the video which I was finding at exactly I got the same. The way of your explanation is also superb
Thanks for the kind words :) Glad you fount it useful!
I always love ❤ your videos good job men
Thanks a lot for your video, it's very useful and informative ☺️
Very useful information I was looking for !
Great tutorial - Q - How to get the delta between two Tables - using PQ - example: Need to know using an SW assets inventory Table, a client that has not been installed using the client current comparison Table. I must show the delta only using the Asset Name as the link field.
Thank you for the video but I want to know how will you know if data in one worksheet is present in another worksheet. Since in this video your working on the difference between two cells. Example. Let's say we have the figure 10 in cell A2 and 10 is present in another cell, let's say A4 in another worksheet. How will you know that the figure is present in that cell. Looking forward to your reply. Thanks
If you are looking for only one colum on each worksheet i think a vlookup would work
Very very useful info 😊thank you sir 😊
Good video, do you have a formula that will compare an entire workbook of 12 pages to a separate worksheet with numbers and names? I need the ones that match to be highlighted.
Simply outstanding!
Great tips.
Liked & subscribed.
Thank you for sharing.
Thank you Sumit for the tutorial.. Very helpful.
Glad you found it useful!
Very well illustrated.
I found this video very helpful. 👍
Amazing 👏. I was in need of these features in my process. Thank you so much 🙂👌
Super and useful information..Thank you!!
Really it is a mind blowing tutorial on excel. I was looking for it. Thanx u made my large work in easy.. more excel tutorial pls
Glad you found it useful!
Thanks for the tips!
Is this only for the comparison between the data in the same row from two workbooks/sheets, i.e. Row 1 to Row 1 and etc.?
I have two different workbooks - Workbook A and Workbook B.
Workbook A is a full list of a set of data that I exported.
One of the columns in Workbook A contains identical data as one of the columns in Workbook B, but they are all in different rows.
Is there any excel formula that we can use when we are comparing data in different rows?
Hi Shannon, did you get a reply back from Sumit Bansal? If not did you figure it out? I would be interested in knowing how to compare 2 files successfully. Thanks.
I am facing the issue of for highlighting the changes
You cant use references to other worksheets or workbooks for Conditional formatting criteria .
Please help
Thanks for this great trick!
Note to myself: CHAR(10) = Add Line Break in Excel Formula
I didn't get it. Why char(10) used?
@@av7829 that function is used to put the text in another row but in the same cell, that's why you can see the Feb values in another line or row below the Jan values.
For complicated and high volume reconciliations with multiple files, I have been using Foxpro programs since a long time....
Great video, thanks. I have 2 sheets (previous and current month reporting) each have an unique ID field and I want to compare the dates to find if any dates have changed. The current month will have new rows of data and some of the previous month data (completed items) will be removed. Therefore the data sets are not matching using a filter on the unique ID. Any advice?
I too am curious. I have a similar issue but with tracking incoming and departing employees
Edit: so I found that using IF(ISNA(VLOOKUP helped me. I can't post snips of my sheet but you can reverse engineer my formula below:
IF(ISNA(VLOOKUP(B62,[ARRAY NAME],1,0)),"REMOVED","NO CHANGE")
Don't forget to change the cell to whatever it is in your sheet
From ekm: E-verification,
Drill bits, painting brush,submersible heater,chisel,chintheru, Mara ചുറ്റിക, projector motor and transformer. Old fan motor, cheriya vaakkathi, long drill bit, nylon, uli get ready, Rajesh license, അറക്ക പൊടി
Very useful video...please upload video on different price comparison&analysis of report pictorial representation in charts
Hi Sumit -If we want column A to be displayed in the difference tab? secondly explain us a sample of multiple columns comparison/differences.
Thanks, Sir. I am not able to compare two excel sheets. Error is showing as : "You cannot use references to other worksheets or workbooks for conditional formatting criteria" Sir, please give solution.
What store were those changes in? What is the change and what is the store number the changes relate to? Could take this video and take the next step in relating the store number? Thanks
Nicely explained
Very good 👌 thanks
a scenario where store is in a different order or may not be in incremental order then how do we compare two spread sheets considering store as the primary key.
This is Golden nice one guys, I love the conditional formatting to get the difference between months.
This saved me so much time thank you
Glad you found it helpful
You changed my mindset about indians
Very informative.... thank you.
Hi Brother, How can we compare text columns with ignore case? Means ABC and abc should be equals and should not get highligted. Thanks in advance
Very nice. Many thanks sir
HI thank you this very helpful , what if I have three work sheet and I want to do match , I tried but it dose not work , any suggestion ? thanks
Awesome, Thank you more..
Can you please make video on writing user defined formulas under conditional formatting option... Thank you!!
Very useful topic. Thanks sir
Thank you for videolesson!
How about if I have master list about 50 records that I want to compare with 2nd list has 3 records? how can I do the comparing? plz advise
Superb as ever, thank you
Thanks for commenting Bill... Glad you found the video useful!
Superb Yaar. But sir it can only happend when rows are equal in both sheets. Will it work if there are differences in rows..
Same doubt bro
Hi sumeet your all video are very good to update my knowledge in excel.
I have following query please update
* I have some customer data in different-2 sheets where column heading is same. how i can merge data and keep them in one sheet.
Example: Sumit Bansal is my customer.
In one sheet Sumit Bansal and his company name is present and in another sheet his contact no.and in 3rd sheet his email id and website address is present.
kindly advise how to merge all three sheets.
Sir how if selected from drop down list then data can be copied into that selected sheet and the current opened sheet same time
If i want to do conditional formatting , what if additional column is added then the result will be mixup how to resolve the same
You cannot use references to other worksheets or workbook for conditional formatting criteria error being shown. please help
I have an employee headcount of 2020 extracted from an HR platform and a new employee headcount of 2021 from a new HR platform. Both can be formatted as xlxs or csv. I need to find out what employees show on the 2020 list that we do not have on the 2021. Those employees for 2020 need to be added to the 2021 headcount list. How do I do that?
The conditional formatting instruction was excellent. Exactly what I needed. Enjoyed the other information as well.
Too complicated for me. But if you have the patience to learn the possibilities of excel are truly astonishing.
Can you compare more than 2 sheets and pull the differences? Is there a way to use VBA to be able to repeat this process regularly?
very helpful! thank you
This is a brilliant video. Thank you so much!
Great! even with my experience with excel, I haven't done anything like this before. Thank you bro
How do i insert a new row at the end of the data and have the sum total update, most of the time the new row is not included in the total, plz help
Great tutorial again! Thanks a ton for this Sumit!!!
I have a different problem but do need comparison. I have 2 Excel files. File A is a list of all of our customers, total 2000 lines. File B is a list of all of our customers who purchased from us in 2019, total 1675 lines. The customer numbers are all in column A. so i just need to compare column A on both Excel file, highlight the customer # on File A that are not on File B. so i can eliminate our Dead customers on File A. Any idea? Thanks
i really want to master Data but i always find people do small data whilst my data that i try to work on its huge and dont know how to go about it
Agree, most of the time when I am creating videos, I tend to use small dataset as these are easier to demo. For large data, have you checked Power Query. It's amazing and can handle large dataset with ease. Again, depends on what you are trying to achieve
Hi TrumpExcel, i enjoyed your presentation. However, can you handle SPSS like this Excel tutorial?
This is Golden nice one guys.
when the tutor has an art of complicating simple things, the lesser said is better.
I would make a vlookup formula in the column c of jan. And in the column d is the differnce. It is more easier. Or a summary sheet.
Please give a video,it would be helpful for me.
I tried and I get "FLASE" for all .. trimmed just to two columns to replicate exact this as I was excited that we can give table's first cell to compare with another table's cell and can be dragged .. Immensely useful, if I get this working..For me the formula is working when I point to the column of comparison in this case B1 ( with Headers).
What if there are multiple columns. Like in I want to compare date, trans no., item, qty. Plus it is not in same cell
Does it work for content matching of two webpages? I have long text from two sites I need to compare and find only the mismatched word/words and print it. Can we do that or it will return the entire text even if there is a single word mismatch and rest all is matching?
Thank you so much for your tutorial! Your tips a very helpful!
@Trump Excel I followed along your instruction and enjoyed every minute of it. Then I got stuck where the need to be able to highlight differences in data when comparing both sheets (in the same workbook). A pop-up message appeared "You cannot use references to other worksheets or workbooks for Conditional Formatting criteria". I am using Excel 2007. Please help! Thank you in advance for sharing with us this valuable tips on using Excel. I have Like and Subs to your channel :)
I also got the same msg. in Excel 2007.
It works in Excel 2016. Probably the function is version specific
@@mahamohan1 ah, that probably it
You cannot use references to other worksheets or workbooks for conditional formatting criteria- what should I do?
Extremely helpful! I wish I have learnt this earlier! Thank you so much!
Great video. Thanks. It saved me a lot of time and effort!
Very helpful! Thank you.