Compare Two Worksheets Excel VBA Macro
HTML-код
- Опубликовано: 11 дек 2021
- In this video we see how to compare two worksheets with two different versions of data in Excel using VBA macros. Thus, we can see if there are any changes in version 2 compared to version 1. The changes are highlighted in yellow. Find the code to compare two worksheets with Excel VBA in the Excel Macro Class blog under the following link: excelmacroclass.blogspot.com/...
You can find many other macro examples and Excel VBA learning materials (including a training for beginners) in the blog under the link: excelmacroclass.blogspot.com/
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)
Such clever code ! Thanks 👍
Thank you!
Thank you so much
Thank you sir, it helped me alot
Thanks 👍
Keep it up bro
Thank you sir. If I wanted to take those highlighted differences and then have them show in a new sheet(3rd sheet) looks like I would say "Then" after the vbYellow. Followed by "Worksheets.Add selcell.values ?
Add the third sheet after the last sheet and do it before the For Each loop, and before activating worksheet1 ( Worksheets.Add after:=Worksheets(Worksheets.Count). Then add a counter for differences (difs=difs+1 in the line after vbYellow, and another line with Worksheets(Worksheets.Count).Range("A" & difs).Value = selCell.Value
(or simply Worksheets(3). But at this point is probably better to refer to sheets by the name. Hope that helps!
Thanks for the excellent video. I was facing an issue in case the two sheets don't have identical data for the 1st column say there server name in your case can we modify the same compare to check for the ID first and then compare the cells.
You probably want to check this other video: Compare Two Columns (ruclips.net/video/UvnefIMtNnM/видео.html). I have answer a similar question in the comments there, I wrote some code to check for the value in other column, for duplicates in the first column. Have a look at that.
Thank you! What if you want to put the differences on another tab instead of highlighting them?
That's covered in some of the videos of the "Compare Everything in Excel" mini-series. Check out the first one here:
ruclips.net/video/oTzY44uou1Q/видео.htmlsi=rM3WAX0ew6q_hpTn
But I think that's specifically covered in 2.1 or 2.2, see here:
ruclips.net/video/jyZHOIbHtv8/видео.html
ruclips.net/video/T4QMCoFlVdc/видео.html
This is really fast Code! I do have a question though, when I set my Set dataRng = Range("$BL:$BL"), so it just does one column, it just dogs down the speed in which this executes. Is there a reason for that?
A single column has more than a million cells to compare (despite most may be empty), and that's probably much more than the number of cells with content in your data range as taken with CurrentRegion. That's why it takes longer. Hope that helps!
What if the column headers are slightly different or it contains more columns in one sheet verses other? Will this cause an issue. Must they have the same identical column name? I’m not the best in excel but if I have to this manually it’s gonna take me forever and I have to start doing this project every week ugh 😩
If headers are different, they will be highlighted in yellow, any cell with a different value/text is highlighted in yellow. If the first worksheet has more columns/more values, those will be highlighted in the empty cells of the second sheet, but if it's the other way around (more columns/values in the second sheet) that will not show, unless you change the code and activate the second sheet at the beginning of the macro instead. The macro can be adapted as per your needs. You can probably automate all that weekly work you have to do, check out other videos, or the tutorial for beginners, and once you get it, is gonna pay off with your routine work. Good luck!
Any conditions have to update to check for selected columns/cells and not all columns or cells?
You can first loop through columns, and if the condition is met (for example, the column name or header is X) then you loop through cells. For example:
For Each col In ActiveSheet.Columns
If col.Cells(1, 1).Value = "X" Then
For Each cell In col.SpecialCells(xlCellTypeConstants)
'compare code here
Next cell
End If
Next col
What if the data's are in different cells?,is there a way to compare data in diff. Cells?
You need to offset to the relative position between cells, or specify the range in Sheet1 vs the range in Sheet2. This routine just compares the values in cells from 2 different worksheets.
Does it compare the entire record from Ver 1 with Ver 2 having the same order of columns in both the sheets.??
It compares cell-to-cell, so it doesn't really matter what's the order of columns. It compares if the value in cell B4, for example, in ver1 or sheet1, is the same as the value in B4 in the other version/sheet. If you want to compare data in columns check this other video: ruclips.net/video/UvnefIMtNnM/видео.htmlsi=Ffzux0E0daeyGTeu
Hi...how to high light the column headers if there is a mismatched value in the cell ..?
Following the code in the blog post, you just need to change this when the condition is met:
Worksheets(2).Cells(1, selCell.Column).Interior.Color = vbYellow
Blog post code available here: excelmacroclass.blogspot.com/2021/12/compare-two-worksheets.html
what if i want to compare worksheet 1 and worksheet 2 not by cell. as long as worksheet 2 have the unique value as in worksheet 1, its fine.
not sure I understand, so what you want to compare? to know if worksheet 2 has the unique value as in worksheet 1, we need to compare by cell, or what do you mean?
Amazing function for a basic worksheet, but when you have complex workbooks with hidden cells and frozen panes, it seems to only work for a small portion of the sheet and then stops running!
Good point! But I believe you can address that using Set dataRng = ActiveSheet.UsedRange or the specific range (e.g. Set dataRng = Range("A1:M120"), instead of using CurrentRegion around the active cell. Then the loop should work throughout the range regardless of having hidden cells or frozen panes. It would have problems if the worksheet or workbook structure are protected though.
Hi there - what if I want to extract new entries on to a new sheet (i.e the whole row) instead of highlighting?
You need to add another sheet either manually or with code (it should run only once):
Sheets.Add.Name = "entries"
And then you can add the code below when the condition is met (following the code in the post - see description). That copies the entire row with a different value in the second worksheet to the first empty row in the new sheet (entries):
Worksheets(2).Rows(selCell.Row).Copy _
Sheets("entries").Rows(Sheets("entries").Cells.SpecialCells(xlCellTypeLastCell).Row + 1)
@@ExcelMacroMania great thanks!! Not sure if the above will help (perhaps you have another video on this). The new entries/unique values that need to be identified are only within one column (e.g cells in column A~Sheet 1 vs cells in column A~Sheet 2. If an entry in column A~Sheet 2 is not found in Column A~Sheet 1, then that whole Row (from columns A to AZ needs to appear as a row in Sheet 3. Essentially I would need to do this monthly to identify new entries from 500k rows. Much appreciated!
@@AH-xf7qf Then you should probably look at this other video (Compare two columns). Then you can still use the line of code I shared earlier to copy rows to a new sheet when the values is different.
ruclips.net/video/UvnefIMtNnM/видео.html
@@ExcelMacroMania great thanks!! 👍
Hi..in my excel data is not arranged in the order...like if emp IDs are shuffled and want to compare the whole employee data as per emp I'd.how to give that condition in query?
I believe you should look into this other macro to compare two datasets or columns with data. Here the order of the values does not affect the result. Here's the video: ruclips.net/video/UvnefIMtNnM/видео.html
Sir can you please help me with code - Object is i need to reconcile to data between two files using unique id.
For that check this other video: ruclips.net/video/X6KTCuqiZOU/видео.htmlsi=uWfvsWks3eXP9mQS
I am getting Run-time error '424'
Object required
Maybe you have a type in some of the objects (ActiveCell, selCell,..) if you hard-code while watching the video. The code is in this post: excelmacroclass.blogspot.com/2021/12/compare-two-worksheets.html
I am getting compile error: Sub or Function not defined.
You might be missing some object definition in the macro... please check the code here: excelmacroclass.blogspot.com/2021/12/compare-two-worksheets.html
Hi!!! How to use: Workbooks(Book1Name).Sheets(SheetName).Cells(1,1) = Workbooks(Book22Name).Sheets(SheetName).Cells(1,1) no error
Not sure what you mean. Workbook name must have the path and workbook name with extension. Check this other video to compare two workbooks: ruclips.net/video/4lLlX_pXoA0/видео.html
Hello, thanks for the Tutorial! I am trying to compare data from 2 worksheets (different column for each worksheet)
I have used the code below, however, I am getting a "For Without Next Error", and am unable to figure it out.
Sub CompareTwoWorksheets()
Dim dataRng As Range
Worksheets(1).Activate
Set dataRng = Range("B:B")
'Start loop to compare cell to cell
For Each Row In dataRng
If dataRng.Value Worksheets(2).Range("C:C").Value Then
Worksheets(2).Range("C:C").Interior.Color = vbYellow
End If
End Sub
Yes indeed the Next is missing (before the End Sub), but nevertheless, that won't work, the code is wrong. Why don't you use the code from the video? You can find it here: excelmacroclass.blogspot.com/2021/12/compare-two-worksheets.html
Or, if you want to compare the contents in column B vs column C, is probably better to check this other video (Compare Two Columns): ruclips.net/video/UvnefIMtNnM/видео.html