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

**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: ?

_Row\^Col	A	B
1	Customer	Assembly
2	Nu Torque	13456
3	Blu Origin	Spaceship
4	Jet Blue21	ABC
5	Alaska	789
6	Blu Origin	Spaceship
7	Toyota	Supra
8	Emirate	ABC12345

**Thainguyen** · 07-17-2018, 08:34 PM

**DocAElstein** · 07-19-2018, 02:46 PM

Hi Thai,
As there are no duplicate in your worksheet Sheet1 we can consider some much simpler alternatives.

I consider here now a totally different approach, a much simpler idea:

General strategy:
We simply look in sheet 2 for occurrences of the data rows from Sheet1. When those rows are found the position in the output array , arrOut() for that row is emptied.
(The output array is used as previously to store the information to be outputted to us to indicate new or changed data )
Initially the output array is given all the values of Sheet2 row data

For this initial test code I will keep the output simple. I will restrict it to just the new or changed data

Brief code description:
Rem 1
As before the data ranges are taken into arrays. This is simply done as manipulating data inside VBA code is generally more efficient than manipulating the data in a worksheet. Similarly we aim to build up an array, arrout(), containing all the information for the output worksheet and then paste the final completed array out to that output worksheet in one go. Once again, this is done purely as it tends to work a lot faster: Every interaction with a worksheet slows a code down considerably.

Rem 2
We no longer need our continually modified array , arrSht1b()

But we need two new arrays to assist us in doing the checking for the existence of sheet1 data in sheet2. These will be one dimensional arrays, there contents will be made up of the concatenation of the data. This data will be checked for
So for example, in the array to check Sheet1 , arrSht1Chk() , we have data like
{ “Nu Torque | 13456” , “Blu Origin | Spaceship” , …………..}
arrayForCheck.JPG : https://imgur.com/8tw5L61
arrayForCheck.JPG

'2c We also now need to fill the array for output , arrOut(), initially with all the data from Sheet2. This is because our aim is to remove data from this , if it is present in Sheet1

The main looping, Rem 3 , is now totally different to that previously.
We have a much simpler process:
__We loop For every row in Sheet1, ( previously this loop was for all rows in sheet2) ========
__At each row we now do something completely different to previously
__Initially we look to see if we can find the data from Sheet1 in Sheet 2: This code line,
__ MtchRes = Application.Match(arrSht1Chk(Cnt), arrSht2Chk(), 0) ,
__will return the position along where this data is found if it finds it. If it does not find it then an error is returned.
_____we now have a second Inner Loop ----------------------------------------
_____This looping continues ( or is done the first time ) as long as a match is found.
_____The main part of this coding within the Inner loop is to remove the data from the array for output if the current row data from Sheet1 is found in the Sheet2 data.
_____ ( There is some extra coding to catch any duplicated data in Sheet2, and indicate that in the Output )

_.____________

Please try this code out and report back
Once again I have not tested it thoroughly.

Alan

Here are some sample results: http://www.excelfox.com/forum/showth...0742#post10742

Here is the code: http://www.excelfox.com/forum/showth...0743#post10743