Vba button Comparison data between two file or two tabs

**Thainguyen** · 06-30-2018, 03:39 AM

I got a template layout and i normally copy and paste all customer data in my template and saved. However, a lot of time customer send me same data but they added in few new data. But some of the old data is not the same row or cell as the old one.

I have code that scans and compares old excel list with new excel list, and then pastes all of the differences between the two into a new sheet. My code is working properly - However, This code is only compare cell by cell. It don't work if there is a new row inserted between old rows.

How can I improve the code to look only for new data added in the new excel file and avoid display all the same data that listed on different row? What should I change or add in my code?

I've tried researching on Google and Stack Overflow, but couldn't find much relevant information.

Here is my full code (let me know if you need me to provide more):
image(1).jpg image.jpg

Code:

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

Private Sub CommandButton1_Click()
'Compare2WorkSheets Worksheets("Sheet1"), Worksheets("Sheet2")
Set myWorkbook1 = Workbooks.Open("C:\familycomputerclub-website\Excel2007    \testcompare2.xlsx")
Compare2WorkSheets Workbooks("testcompare1.xlsm").Worksheets("Sheet1"),     myWorkbook1.Worksheets("Sheet1")
End Sub

**Thainguyen** · 06-30-2018, 03:39 AM

**DocAElstein** · 07-01-2018, 03:06 PM

Hi Thai,
The biggest problem, I find, is always trying to understand what you want.
The screenshot images are very difficult to see. Also I cannot copy data from them.

Please supply Example 1 and Example 2 again.
Please give two workbooks,
one for example 1,
and
one for example 2.
( or give extra worksheets in a single file to show more examples of what you have Before and what you want After )

I do not think this is a difficult problem to solve. But I think you need to try to explain again very carefully and in full detail what you want.

You need to spend some time to give a clear “picture” showing what you have
Before
and what you want
After

But remember.. an image is good but only in addition to workbooks. Please give workbooks, ( or more worksheet examples to show what you have
Before
And what you want
After )

As always, it is very easy for you to understand because you know your project requirement well.
But for someone else who does not know your project you must try to explain a lot more clearly.
In a forum giving free help you must try harder to make it easier for the helper to understand your project and requirement.

Alan

**DocAElstein** · 07-01-2018, 03:06 PM

Hi Thai,
The biggest problem, I find, is always trying to understand what you want.
The screenshot images are very difficult to see. Also I cannot copy data from them.

Please supply Example 1 and Example 2 again.
Please give two workbooks,
one for example 1,
and
one for example 2.
( or give extra worksheets in a single file to show more examples of what you have Before and what you want After )

I do not think this is a difficult problem to solve. But I think you need to try to explain again very carefully and in full detail what you want.

You need to spend some time to give a clear “picture” showing what you have
Before
and what you want
After

But remember.. an image is good but only in addition to workbooks. Please give workbooks, ( or more worksheet examples to show what you have
Before
And what you want
After )

As always, it is very easy for you to understand because you know your project requirement well.
But for someone else who does not know your project you must try to explain a lot more clearly.
In a forum giving free help you must try harder to make it easier for the helper to understand your project and requirement.

Alan

**Thainguyen** · 07-02-2018, 08:11 PM

**DocAElstein** · 07-04-2018, 02:16 PM

**DocAElstein** · 07-07-2018, 02:17 PM

Hi Thai,

Something like this requirement can be done in very many ways in VBA. I only know a few of the ways.

I find it very difficult to understand and follow the logic of your coding.
I think it is always difficult to understand other people’s code.
So for now I will start from the beginning with a new code.

It is not 100 % clear to me exactly what output you want. It appears that you want to show the new data in the second sheet, along with the original data… This seems strange to me?
For testing purposes, I will give the output a little bit different to how your code does it, because that makes it a bit easier to test for now,

I think you have potentially a very complicated situation. It might be more sensible to insist on certain rules for those adding data, in order to simplify the possible scenarios.

However, I will make some attempt on a code

I give you the code first.
Check if it does what you want.

Then if you need modifications or want me to explain anything , then let me know

I assume a customer will add an entire row or change data . This makes the logic very tricky to get right. Not all possible scenarios will be covered with my code: My code may not work in all possible scenarios

My general code logic:
Both data ranges from workSheet1 and workSheet2 are captured and put into an Array, arrSht1() and arrSht2().
I make a new array, arrSht1b() , which is intended to have possibly extra rows at the place where new rows appear to have been added in workSheet2. So it is given the size of arrSht2()**
Initially the values from arrSht1() are placed in arrSht1b(). ( But as arrSht1b() has the size of arrSht2() , then initially there may be some empty elements at the end, ( end = last rows of data )
I also create an array, arrout() , to be used to hold the required output data.
**Both arrays, arrSht1b() and arrout() , can be given their types ( String ), and final size as that of arrSht2()

I loop through the data in sheet1 ( or rather array, arrSht1b() ) and compare with sheet2 ( or rather arrSht2() ).
( arrSht1b() effectively monitors, ( is adjusted to ) , become equal to the new data as the looping continues) .

A Condition check is made.
__If either cell in a row from the sheet 1 data does not match the new data, AND the next row from sheet1 data does, then I assume a row had data changed, but a row was not inserted.
(arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) = arrSht1b(Cnt + 1, 1) And arrSht2(Cnt + 1, 2) = arrSht1b(Cnt + 1, 2))

For this condition satisfied, the monitoring array , arrSht1b() , is adjusted to the new values and the cell differences checked and counted and the relevant info given to the output array, arrOut().

A second Condition check is made, ( which will only be made else if, ( ElseIf ) , the first was not satisfied)
__ElseIf either cell in a row from the sheet 1 data does not match the new data, AND the next row in sheet2 data does not match the next data from sheet 1, then this is one possibility** that a row has been inserted/ added
((arrSht2(Cnt, 1) <> arrSht1b(Cnt, 1) Or arrSht2(Cnt, 2) <> arrSht1b(Cnt, 2)) And (arrSht2(Cnt + 1, 1) <> arrSht1b(Cnt + 1, 1) Or arrSht2(Cnt + 1, 2) <> arrSht1b(Cnt + 1, 2)))

I then add that data row then to monitoring array, arrSht1b(), after all values above are shifted one place. This modified array is then further used to compare further with array, arrSht2()
At this check , the value at the row in the arrSht2() is added to the Output array, arrOut()

Eventually arrSht1b() will be identical to arrSht2(). But, the important point, in the process of looping it is built, and at each loop is used to check if the current row matches the row in the second sheet data.

_.___________________

This is almost certainly not the most professional or efficient code, and it will not work in all possible scenarios** but may be easier to understand as an initial attempt.
**I have not tested it extensively in all possible situations.: I leave this to you to test thoroughly and tell me if it is working OK for you, ( free excel help forum is for help and to help learn but not to do work for you )

I have not included all your original cell formatting. If you need help with this then ask

For testing purposes, I give the output in a third worksheet, Tabelle3

I will gladly explain the code in more detail if you ask me to.

_._____________

Code is here:
http://www.excelfox.com/forum/showth...0736#post10736
( Run code Sub TestieCalls() )
( code also in uploaded files:
Compare2WorkSheets Testings2.xlsm”
Compare2WorkSheets Testings1.xlsm” )

_.______________________________________

Example testing
For testing purposes, I give the output in a third worksheet, Tabelle3

Test 2 ( “Compare2WorkSheets Testings2.xlsm” )

Sheet1	Sheet1	Test Output	Test Output	Sheet2	Sheet2
Customer	Assembly			Customer	Assembly
Nu Torque	13456			Nu Torque	13456
Blu Origin	Spaceship			Blu Origin	Spaceship
Jet Blue12	ABC456	_____ <> Jet Blue12	_______ <> ABC456
Alaska	789			Jet Blue12	ABC456
Toyota	Supra			Alaska	789
Emirate	ABC12345			Toyota	Supra
				Emirate	ABC12345
		Spaceship on last row is new	12 on last row is new	Spaceship	12

Worksheet: Tabelle3
Testing2.JPG https://imgur.com/ISbeHaJ
Attachment 2081

Test 1 ( “Compare2WorkSheets Testings1.xlsm” )

Sheet1	Sheet1	Test Output	Test Output	Sheet2	Sheet2
Customer	Assembly			Customer	Assembly
Nu Torque	13456			Nu Torque	13456
Blu Origin	Spaceship			Blu Origin	Spaceship
Jet Blue21	ABC	Jet Blue12 <> Jet Blue21	ABC12345 <> ABC	Jet Blue12	ABC12345
Alaska	789			Alaska	789
Toyota	Supra			Toyota	Supra
Emirate	ABC12345			Emirate	ABC12345
		Spaceship on last row is new	12 on last row is new	Spaceship	12

Worksheet: Tabelle3
Testing1.JPG : https://imgur.com/eCjAGOL
Testing1.JPG

_.__________________________-

The above results seem OK....
**But remember: This code does not work in all scenarios. We may need to modify and improve it. You must check all possible scenarios and report back.

Alan

**Thainguyen** · 07-17-2018, 04:10 AM

**DocAElstein** · 07-17-2018, 02:43 PM

Hi Thai,
I think my current code is only considering the possible addition of one line.

To allow for more possibilities will be quite complicated.
I will look again at this when I have the time.
_.____________

One small question in the meantime:
Is it possible that you will have duplicated entries in a sheet, such as this: ?