Vba button Comparison data between two file or two tabs

[DocAElstein]

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