Compare two Excel Worksheets Cell by Cell Using VBA
HTML-код
- Опубликовано: 23 июл 2024
- How do you compare two worksheets cell by cell in the same workbook or in different workbooks? Excel VBA provides an elegant solution.
First we define a few variables that can count the rows and columns so that we know how many cells are in use in the Excel worksheets. Next we assign the used ranges to the sheet with the maximum values.
Now using a nested 'for loop' we access each cell in the used range and compare them. If the cells values are not equal we write them into a new workbook and also count the difference so that we tell the user about the number of cells that were not the same and also display both the values in the compared sheets. The displayed values are in the same cells in the new workbook as they appeared in the compared worksheets and highlighted appropriately.
We also use a command button so that the user can perform the task with a single click!
You can find the complete macro code here:
www.exceltrainingvideos.com/c...
Thank you for taking time to post this.
It was a great introduction to power of macro in VBA and I appreciate someone taking time to share these tips. I am also using your code to create my own macro.
Hello Dinesh kumar sir, thank you so much for helping us through this video. I used your code and it helped
Great video presentation on comparing two worksheets. Thank you for your time and clear presentation.
Thank you for sharing the Video
My pleasure. Please share my RUclips channel with your friends too.
Thank you for this great video. One question, how would you handle the case where the 2nd workhseet contains several times the same value (like bank transactions for instance) and we need to compare with the most recent occurence.
Hi Sir, Thank you for this video. I need to compare two different worksheets and list out the mismatch data between both the sheets instead of highlighting the mismatches.
Can you pls share the code for listing out the mismatches ?
Many Thanks
thank u sir
It is possible to cut and paste a row from table 2 having the same information by the side of a row of a table 1? Cut and paste will allow only those rows not identified with table 1.
Nice Video- I want to know how can be display difference in report when cell in one sheet is empty and other has a data - I an getting an error when comparing sheets under such scenario
Excellent Video with step by step explanation of everything. Thanks million Dineshji..
I need your support on a macro which i am building. Requirement:: Take data from one column of an excel(We need to compare all the values in the column) locate in another excel and extract other corresponding columns to a new excel.I m unable to crack the code for taking value from 1st excel locating in second excel and extracting the other columns and paste them to different. I have tried numerous codes but getting stuck in one way or the other.... U r support/guidance will help reduce my working hours hugely...
Thanks a lot sir..
For your numerical data you can define the variable as double and check it out.
Sorry sir I'm new on using vba can i check with u that if i want to compare with two sheets and send the error data to another workbook. How should i do that
Plus it has many column which i do not know how to code it do u have learning video
Sir, how could make that to compare 2 statements of accounts ?
Hi Dinesh,
I have copied the code and tried to adapt by adding some scripts to input the data from two CSV files and perform the table comparison. Somehow, I encountered the error code "#1004 application defined or object defined error" and the script broke at the point of the following statement.
Set rngO = Worksheets(ksWSOriginal).Range(ksOriginal)
Do you know why ?
Good video! I need some assistance. A peer and I are trying to find out how to do a cell comparison between vertical rows; and then update the cells if any unique values exist between each row; then roll the rows into one. I'm new to VBA. But have some basic clarity around how to plan out the code. Is their an online reference to view to get me started?
Hey Thanks for the code, Kindly help on how we can run this vba in ms access to perform the same task? I followed the same procedure in access but didn't worked.
I have a worksheet with headers of Employee Name, Job, Resource Code(Their specific job code), Date of Birth, and Location. An updated worksheet gets sent monthly that shows any changes in location, job class, etc. The updated worksheet also shows if there were any new hired or terminated employees. How would I combine the two worksheets together so that the original worksheet will have all of the changes that were made in the updated worksheet, including the addition of the new terminations and hires? Essentially what I need is all of the updated data to delete and replace the outdated information in the original worksheet, while still keeping the unchanged data on the spreadsheet. Also if needed a new worksheet showing the data change can be done. What I need is something like the compare and merge workbook setting in excel, but more advanced.f this doesn't make any sense please ask and I can clarify.
Thanks so much!
Thank you sir . I am able to compare two cell values . I have one more question Eg : sheet 1 A1 cell has "Worksheet " and B1 cell has " Sheet","Worksheet","Normal" on one by one order in same cell . Can we compare these three values against A1 cell values ?
helpful video sir .One Worksheet values are already stored and another sheet values are dynamically getting posted ,so now how to compare those values .can u please tell me sir.Thank you
Thanks sir the problem is when I executed the macros a msg box raises with ok & cancel button I want to click on ok automatically I have tried application.displayalerts = false and even sendkeys also but no effect on it kindly help with the code.
check your code thoroughly. Also check whether you changed the code of the command button appropriately. You can also visit the web-page to copy and paste the complete code: familycomputerclubdotcom/compare-two-excel-worksheetsdothtml
Replace the two 'dot' words with a '.' .
@dinesh sir can we compare any two excel sheets using these code ?
I get this error "With ws1.UsedRange" Bug please help
Firstly thank you so much for such a clear and detailed explanation about the code....I am looking for almost the same functionality but not exactly the same....i would like to highlight the differences in the same sheet...how can i tweak the code?
TIA
mounika pharma Maybe this video helps: @match-data-from-2-worksheets-highlight-differences-using-collections-in-vba/
Dinesh Kumar Takyar Hi, I'm trying to do the same as Mounika but your answer above looks like the link is not right. I'll appreciate your reply.
Agreed it didn't worked even for me the code is going to an infinite loop...
Thanks sir. What is we want to loop through a set of file pairs and compare? can you please post that as well?
Noted.
hi i have a doubt what should i do to highlight the cells which does match in sheet one ? can you please advise on this
Use conditional formatting for such a condition.
Thank you for your videos. They have been very helpful. I am having trouble with this particular code. I have copied it correctly into a workbook module but can not get it to run. Each time I run the code the macros list window pops up asking me to enter a name to create a new subroutine. I don't know what I am doing wrong ... please help. Thank you.
Kevin St John Did you save your file as a macro enabled file with the extension'.xlsm'?
Hi Sir, this is an excellent macro. A little info needed as how we can call two different excel reports as inputs to this macro. In the sense, the macro should ask for input of excel work book 1 and work book 2 and then do the compare
Use an inputbox. Examples are available in these videos:
ruclips.net/video/MfO1p_ErJfk/видео.html
ruclips.net/video/78QHYGCrb4g/видео.html
Please give feedback.
hi Dinesh, thank you for the code. It is working good. however, I am running another issue. A lot of time i have to compare old data to new data from same customer but the data is the same but it has been place in the different row on both file. I wonder how to use your vba code check for new data enter in file not the row.
Think about the algorithm - each step = and you'll find a solution in the looping process.
This link will guide: www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/
I need VBA macro which would get values from two folders and multiple named files (*.xlsx and *.xls) with different named multiple worksheets in them.
This link will help: www.exceltrainingvideos.com/copy-data-from-one-workbook-to-another-to-a-specific-worksheet/
Or search www.exceltrainingvideos.com
What you're trying to do is:
(1) Loop through folders and open the two folders one by one:
(a) www.exceltrainingvideos.com/how-to-open-folder-in-excel-using-vba/
(b) www.exceltrainingvideos.com/how-to-loop-through-subfolders/
(c) www.exceltrainingvideos.com/tag/copy-specific-files-from-folder-and-subfolders-into-destination-folder-with-vba/
(2) Copy data from multiple worksheets into another workbook:
www.exceltrainingvideos.com/transfer-data-multiple-workbooks-master-workbook-automatically/
I m having hard time to understand the command button. its not clear how you got the VBA screen once you click.
This link will help: www.exceltrainingvideos.com/command-button-excel-vba/
You can also search www.exceltrainingvideos.com or my RUclips channel goo.gl/5Jx1NP to get more details on Activex and Form controls.
Hello sir , im getting the differences populated in sheet 1 itself re writing again and again and new workbook is empty.
Please check your code. This link will help: www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Sir need help in creating macro for comparing values in two sheets and pasting the results in one sheet if received or not received
These VBA tutorials will help:
www.exceltrainingvideos.com/compare-2-worksheets-to-create-report/
www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
You can also search this channel or our website www.exceltrainingvideos.com.
A little hard-work has never made anybody unhealthy!
Sorry, but I am very new to VBA. What exactly is the variable in this code and where is it located?
Hello Sir,
Thank You so much for your excellent Video with step by step explanation of everything. This is was my first VBA code and i nailed it only through your help. I needed only a small help from you. What we need to do if we can highlight the mismatched data on the same sheet without popping out an extra sheet. Appreciate your help in advance.
Can you share a sample of your data?
@Dinesh Kumar Takyar Hello, thanks a lot for this great code: However; i can't seem to make it work, getting en error "Object required at " With ws1.UsedRange" Can you help me with this ?
@Dinesh Kumar Takyar Thank you so much sir for the reply !! Just i am not sure how will i be able to share my data.
Taking your example, I want Andrea & Amelia to be coloured Red in the Sheet 1 & Sheet 2 respectively, rather than popping a new sheet for the results.
You can use conditional formatting.
Hi Sir..
I need this similar comparison method between two workbooks.
Workbook 1 has 12 columns and workbook2 has only 8 columns .
First I need to match the headers of the column of workbook 2 with workbook 1and then as per the column header name match, I need to copy the data of that particular column and then paste it in the similar column of second workbook.
How can we perform this .pls help ??
These tow links will guide:
www.exceltrainingvideos.com/nested-do-while-loop-instr-function-in-excel-vba/
www.exceltrainingvideos.com/how-to-copy-column-data-into-another-workbook/
Or search www.exceltrainingvideos.com
Is the code is mentioned some where, from where i can copy?
The link to the VBA learning tutorial will help: www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Just don't copy and paste but also check the code for any 'undesirable' characters before using.
Hello Dinesh - This is really wonderful & helping many people .. I used your above code & it's working fine as well, Only problem I have is I need to display same header which I have in Sheet 1 OR the Source sheet on the report sheet . Will appreciate if you can help on this ..Thanks Vaibhav
Search my channel or website for example: www.exceltrainingvideos.com/transfer-specific-data-to-specific-sheets-automatically/
+Dinesh Kumar Takyar
Hi sir
is it possible to do reconciliation without common referance
Sir, I have a master sheet in excel from which I want to pull out relevant data on daily basis. Based on ID (better if I can put 20-30 id at a time ) I want to get compete for row data automatically without opening (its big file) my master sheet get written in new excel. Plz, help me. Instead of VBA can we use Python or R bcoz I have a basic understanding of these 2 platfroms.
That's a great project! You'll have to study a few videos. This link to Step by Step VBA tutorials will help: ruclips.net/p/PLFoKoDG_7gtKx1KcXPYOJ6APhcbNCRIDl
You can also search my RUclips channel or website: www.exceltrainingvideos.com
Hi Sir, I have question two different excel sheets and have the same data and also different data. The data is not arranged properly. Now i need to compare both the workbooks. In both the sheets column names are same. For example Name. There is name in Excel in Cell A1 but the same name is present in A31 in another excel. How to compare this. Please provide me the code for this.
Once you write the steps of your actions one-by-one, you'll automatically find the solution. Search www.exceltrainingvideos.com
thank you for your videos Sir. when I copied the - colvall colval2 - Then is coming up in red with the following messages: Compile error and expected expression. I have the latest version of excel and I must add that this is my first try with VBA. thank you very much again and all the best.
This link will help: www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Gutsy to try out such an advanced example as your first VBA code!
THANK YOU VERY MUCH !!! 1st for reply to my enquiry and secondly I found the solution on your link above!!!! I copied and pasted and worked like magic!!! thanks again and all the best you are genius
Hello Sir... How to compare same cells with different values.. for eg: name of 2 person is same and having different phone number.. how do I compare this case?
Use an 'IF' statement like so:
If person_name="Rajasree" AND phone_number="1234567891" then
I am using this code to compare two sheets and to write differebce result in new workbook.
But there is something wrong.
New workbook is getting generated but it is empty. Comparison result is coming on the same sheet which I am using to compare.
New
Hey, extremely helpful tutorial and thanks for posting the code as well. I am using it to compare two different data sheets where the numbers are very similar except for very small differences in the thousand decimal place. For example
46.7409198117 and 46.7409198116839
I am looking to find a way to only find differences that are 4 demical points or greater. Basically, if the difference took place in 46.7409, I dont really care about the smaller decimals. How could I do that using your code?
Sam,
I realize this comment is 5 years old, but I felt compelled to respond.
All you need to do is a little math with your numbers, to see if they're different beyond your decimal threshold.
These are a couple of his variables we'll need to update:
Dim maxrow as long, maxcol as Integer, colval1 As String, colval2 as String
This is the If statement in his code that compares the variables
(this also gets updated):
If colval1 colval2 then
difference = difference + 1
Cells(row, col).Formula = colval1 & " " & colval2
Cells(row, col).Interior.Color = 255
Cells(row, col).Font.Bold = True
End If
There's two things you need to do. First update variables:
We need to create a variable we'll call "variation" that will store the math.
And we need to reinitialize two of his variables (colval1 & colval2 are strings). We can't do math with strings, so you need to make them doubles so you can do math with them:
Dim variation As Variant
Dim maxrow as Long, maxcol as Integer, colval1 As Double, colval2 as Double
Second, we need to edit Dinesh's If statement. Instead of comparing to see if they're different: if colval1 colval2
we want to do some math with them, and if the variation between the two variables is more than your 4 decimal threshold, then we make note:
variation = 0
variation = colval1 - colval2
If variation > 0.00001 Or variation < -0.00001 Then
difference = difference + 1
Cells(row, col).Formula = colval1 & " " & colval2 & _
" (" & Format(variation, "#0.0000000000000") & ")"
Cells(row, col).Interior.Color = 255
Cells(row, col).Font.ColorIndex = 2
Cells(row, col).Font.Bold = True
End If
First, inside the loop we reset the variable each time with variation = 0
Then, you can see we are now looking to see if the variation between the two numbers is >0.00001 or if the variation is < -0.00001. If it is, it's will populate on your sheet as different.
I also added in Format(variation, "#0.0000000000000") so you can see what the difference was in your output.
I'm sure you either found the answer, or moved on with your live, because this is 5 years old. But I hope it's helpful to someone else.
How do I use this code for csv files?
Can I have the code please?
sir i get subscript out of range error for" sheet 2 "in the command button code. i am beginner can u help me plz.
+Deepthy Prakash
I too face the same problem. let me know the solution
hi sir,i have so much data in sheet 1(from A to AM)but in sheet 2 i have only A to D only (these data i am entering manually )then how it compares the two sheets can u plz help me
www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Hi sir I have successfully excuted my code but it is getting stucked wherein I have to manually click on ok tab sothat macros continues kindly help with the code I have even used sendkeys enter command.
At what point? You may have a look at this link: www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba
Thank you very much for this video Sir, I have an issue with this.. I get a new worksheet opened but it is empty.. but the message box pops up with the number of different data.. How could I resolve this please help me.. I use Excel2010.. Thank you
www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Thank you sir.. I exactly try the same code but not working yet.. Please help
Option Explicit
Sub Compare2WorkSheets(ws1 As Worksheet, ws2 As Worksheet)
Dim ws1row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As String
Dim report As Workbook, difference As Long
Dim row As Long, col As Integer
Set report = Workbooks.Add
With ws1.UsedRange
ws1row = .Rows.Count
ws1col = .Columns.Count
End With
With ws2.UsedRange
ws2row = .Rows.Count
ws2col = .Columns.Count
End With
maxrow = ws1row
maxcol = ws1col
If maxrow < ws2row Then maxrow = ws2row
If maxcol < ws2col Then maxcol = ws2col
difference = 0
For col = 1 To maxcol
For row = 1 To maxrow
colval1 = ""
colval2 = ""
colval1 = ws1.Cells(row, col).Formula
colval2 = ws2.Cells(row, col).Formula
If colval1 colval2 Then
difference = difference + 1
Cells(row, col).Formula = colval1 & " " & colval2
Cells(row, col).Interior.Color = 255
Cells(row, col).Font.ColorIndex = 2
Cells(row, col).Font.Bold = True
End If
Next row
Next col
Columns("A:B").ColumnWidth = 25
report.Saved = True
If difference = 0 Then
report.Close False
End If
Set report = Nothing
MsgBox difference & " cells contain different data! ", vbInformation, "Comparing Two Worksheets"
End Sub
I have 1691 rows and 2 columns in Sheet1 and 1685 rows and 2 columns in Sheet 2
the screen quality of the viode is not good enoug how can i see the code clearly or is there any website i can copy the codes
Try this and let me know if it helped:
www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
TANKYOU VERY MUCH BOTH FOR CODE AND YOUR LINK BOTH OF THE VERY WERE VERY USEFULL
i have one question. i have 2 sheets and they have alot of rows and columns..i have one column common in them. using that column how can i compare data between 2 sheets? can anyone help me
www.familycomputerclub.com/compare-two-excel-worksheets.html
thank you sir for replying me. So i have another issue. I have many columns with some null values. I dont want to see any null values while i comparing 2 sheets. How can i avoid null values? please reply me thanks
Nice code sir, I tried to run the code but always see runtime error 9 & subscription out of range.
Check your code and sheet names.
@@Exceltrainingvideos .. Thank you for the prompt reply, i found that we need to replace the " again. but anyways, a new problem now - ew workbook is getting generated but it is empty. Comparison result is coming on the same sheet which I am using to compare.
I am also getting the same error
@@potluri040 how did u solved error9 ?
Sir, can you please do this for me...I've 2 sheets, in sheet1, 2 columns ,names and status like ali and payer/free and in sheet2 2 columns like name and month e.g anwar and jan/feb...now i want to compare both sheets with a condition that, in sheet 1 only payers are compared to the names of the sheet2 according to the selected month and show the result having the different names of sheet1 in the another sheet like sheet3...i will b grateful..thanks in advance
This VBA tutorial will guide: ruclips.net/video/rb7AZsRMyrc/видео.html
You can also search the channel.
@@Exceltrainingvideos thank you sir, i really appreciate your efforts
Can you share the Macro
It's a good idea to read the description accompanying the VBA tutorial.
How to compare two columns in two different workbook and then update the sheet with not found and also update the rest of the Coloumns according to that sheet
This link will guide:
www.exceltrainingvideos.com/compare-two-excel-worksheets-cell-by-cell-using-vba/
Thank you for your channel, I'm learning a lot. I was wondering if you could give me some guidance on how to accomplish the following data. Any help will be great. thank you
I have the following data:
Sun Mon Tue
D1 Blue Green Black
N1 Green Blue Yellow
E1 Green Black Blue
I would like to displayed it the following format
D1 N1 E1
Blue 1 1
Green 1 1 1
Black 1 1
Yellow 1
Use the 'transpose' data capabilities of Excel as shown here: www.exceltrainingvideos.com/paste-special/
www.exceltrainingvideos.com/copy-data-paste-another-workbook-transpose-automatically-using-excel-vba/
www.exceltrainingvideos.com/transfer-transpose-data-from-one-excel-worksheet-to-another-using-vba/
thank you very much for your channel...could you please advise , how I can compare data multiple rows and column with criteria in different sheets. like: I have a Project number 120050 & 150012.I need summary on PID level for what changes made.
PID
Home Metro
Start date
End Date
Commit%
120050
Boston
6/3/2013
7/12/2013
22%
120050
Gurgaon SEZ
6/3/2013
8/23/2013
30%
120050
Boston
6/3/2013
5/30/2014
1%
120050
Gurgaon SEZ
7/1/2013
3/16/2014
15%
150012
NOIDA
7/1/2013
6/2/2014
5%
150012
Gurgaon SEZ
7/1/2013
10/15/2015
80%
150012
Noida
7/15/2013
4/30/2015
46%
150012
Boston
7/22/2013
7/26/2013
20%
PID
Home Metro
Start date
End Date
Commit%
120050
Gurgaon SEZ
6/3/2013
7/12/2013
22%
120050
Gurgaon SEZ
6/3/2013
7/12/2013
100%
120050
Boston
6/3/2013
5/30/2014
1%
120050
Gurgaon SEZ
7/1/2013
3/16/2014
15%
150012
NOIDA
7/1/2013
6/2/2014
5%
150012
Gurgaon SEZ
7/1/2013
10/31/2014
8%
150012
Gurgaon SEZ
7/15/2013
4/30/2015
46%
150012
Boston
7/22/2013
7/26/2013
20%
See whether this link helps: familycomputerclubdotcom/transfer-data-from-one-excel-worksheet-to-another-automatically.html
Replace the 'dot' with '.' .