Vba button Comparison data between two file or two tabs

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

**Thainguyen** · 07-21-2018, 12:03 AM

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

Hi Thai,

Originally Posted by Thainguyen

Hi Alan,
1. changed the layout of the template and the name of tabs.
2. Can your code only compare column B,C, E, F and G. I don't need it go through A and D.
3. the tab for result. Can we just showing the data different instead of all the data because some time we have couple hundred of rows and it will messy to show everything.
4. If i am deleted some row on sheet2 (NEW). There is notification for that. It should showing on the result as deleted. Or something like part ABC << >> empty.
Please check our email. i have sent you the sample template but with the new layout

Your question:3.
It may be easier and more useful if I explain to you how to do this , because it is very simple:
There are three code lines that paste out all data to the result Tab
Each of those three code lines pastes out 2 columns of data in one go
3(i) To remove data from output
3(i)a) remove some data
Remove or 'comment out the code lines corresponding to the data you do not want to display.
For example, this code modification will remove the displayed data for the original Sheet1 and Sheet2 data

Code:

' Let Ws3.Range("A2").Resize(UBound(arrSht1(), 1), UBound(arrSht1(), 2)).Value = arrSht1()
 Let Ws3.Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()
' Let Ws3.Range("E2").Resize(UBound(arrSht2(), 1), UBound(arrSht2(), 2)).Value = arrSht2()

3(i)b) Remove headings
If you wish to remove the headings ( Sheet1 Sheet1 Test Output Test Output Sheet2 Sheet2 ) , then simply Remove or 'comment out the code line

Code:

' Let Ws3.Range("A1:B1").Value = "Sheet1": Ws3.Range("C1:D1").Value = "Test Output": Ws3.Range("E1:F1").Value = "Sheet2"

3(ii) To change position of output
The range for output is defined by giving the Top left cell of where you want the data, followed by resizing that to the size of the data held in the output array, arrOut().
__ Range("C2").Resize(UBound(arrOut(), 1), UBound(arrOut(), 2))
So if you change C2 to A1 , then you will get your data starting at the top left of the worksheet as you wish:
TopLeftA1.JPG : https://imgur.com/PZAZZlK
TopLeftA1.JPG

TopLeftA1 .JPG : https://imgur.com/PZAZZlK
TopLeftA1 .JPG

_.___________________________________-
Your question:4:
Please try to delete one item on the NEW tab list and use your code. It will not display the different. so it should be good if it can show the different if the item got deleted.
example Toyota and supra listed on A and B column before and now it got deleted.
This is not a straight forward modification to the existing code. This is because the code modifies the check array , arrSht2Chk() , when checking for the data from sheet1 in sheet2

One way to solve the problem is to make a second check array, arrSht2ChkKopie(), with identical entries. This is then used in Rem 3b in a Second Loop ##### similar loop to the Main Loop ============
( this may not be the most efficient way, as this is often the case when modifications are made in a code )
The modified code is here : http://www.excelfox.com/forum/showth...0746#post10746
( For the purposes of the demo I have not made the changes that I explained above in response to your question 3 )

Here is an example of the results of the modified code, if I delete, as you suggested, Toyota Supra from Sheet2 : http://www.excelfox.com/forum/showth...0747#post10747

_._________________________

Your questions, 1 and 2:

Originally Posted by Thai

1. changed the layout of the template and the name of tabs.
2. Can your code only compare column B,C, E, F and G. I don't need it go through A and D.

I do not understand exactly what you are asking. Please explain again and I will look at this later

_.___________________________________
Your question

Originally Posted by Thai

Also, If you can include the display box like the picture below to show how many different item found

This should not be too difficult to do. I will look at this later

_._____________

Alan

**Thainguyen** · 07-24-2018, 03:23 AM

**DocAElstein** · 07-26-2018, 12:37 PM

Hi Thai,
The purpose of this post is mainly to summarise for my and others benefit the current stand of things..

I am responding here to the info I have from you on the from approx 20 – 24th July. I do note that I now have also a more recent layout from you for the result tab, but I only have a small image of that, so let me take this one step at a time, and respond to the request from up until 24th July.
Then we can take this further later.

Let me summarise for the benefit of anyone else following this Thread what I we are currently doing:
Summary of current stand , ( as I see it ).

The previous data Sheet1 and (new) data Sheet2 are now to look like this sort of format:

Previous Sheet1, now tab name is Original
Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G
1	Customer:			Assembly #:			Assembly Name:
2	#	Qty Per	Ref/Designator	Description	Customer PN	Internal PN	Manufacture PN
3	1	1	Nu Torque		13456	456	456
4	2	1	Blu Origin		Spaceship	457	457
5	3	2	Jet Blue21		ABC	458	458
6	4	3	EXCELL123		123	ABC	ABC
7	5	3	Toyota		Supra	460	460
8	6	2	Emirate		ABC12345	461	461
9	7	1	Angel		ABC12346	462	462

Worksheet: Original

Previous Sheet2, now tab name NEW
Using Excel 2007 32 bit

_Row\^Col	A	B	C	D	E	F	G
1	Customer:			Assembly #:			Assembly Name:
2	#	Qty Per	Ref/Designator	Description	Customer PN	Internal PN	Manufacture PN
3	1	1	Nu Torque		13456	456	456
4	2	1	Blu Origin		Spaceship	457	457
5	3	2	Jet Blue21		ABC	458	458
6	4	3	Alaska		789	459	459
7	5	3	Toyota		Supra	460	460
8	6	2	Emirate		ABC12345	461	461
9	7	1	Angle		ABC12346	462	462

Worksheet: NEW

Correspondingly you (Thai) have adjusted the testy Calling code to this sort of form:

Code:

Sub TestyCalls() 
 Call Testy(Worksheets("Original"), Worksheets("NEW"))
End Sub

The columns to check have changed to ……..

Originally Posted by Thainguyen

... display the different between the tabs "Original & NEW" from column highlighted in yellow (B, C, E, F and G)..

In other words , in the form of my previous technique in the code, it appears to me that the “unique” data entry to compare is now comprised of
B & C _ & E & F & G
(Previously this was A & B see for example test results here: http://www.excelfox.com/forum/showth...tabs#post10737

_.____________-

So this post was by way of a summary of the stand.
In the next post soon I will walk through the modifications needed to use columns B & C & E & F & G instead of columns A & B in the comparison

**DocAElstein** · 07-26-2018, 02:57 PM

Hi Thai,
So I move on now …..
The purpose of this post is to walk through extending the columns in the comparison

Some other notes:
***_1 The final output format my not be as you wish. It is better at this stage to check that the correct results are obtained. We can modify easy later the actual result output format
_2 I suggest delaying any code additions to make a pop up message box until we are further as this is relatively easy to do but it would need to be modified at each code development stage. So best not waste that time and just do the job once , when we are further towards the end of the project.

_._________________________________

How to extension compare between data
Two main modifications:.
_1 We need to capture enough data: So we need to capture to include B , C , E , F , G
Conveniently in coding we will capture B-G. ( So We will also have D in the captured data, but we will not use that )
For example code modification:
arrSht1() = Ws1.Range("A1:B" & Lr1 & "").Value
change to
arrSht1() = Ws1.Range("B1:G" & Lr1 & "").Value
So now, our captured data is a 6 “column” array looking like this:
6 Column captured array.JPG : https://imgur.com/8UN2Q7a
6 Column captured array.jpg

_2 Instead of check data A & B we now need B & C & E & F & G
For example code modification
arrSht1Chk(Cnt) = arrSht1(Cnt, 1) & "|" & arrSht1(Cnt, 2)
change to
arrSht1Chk(Cnt) = arrSht1(Cnt, 1) & "|" & arrSht1(Cnt, 2) & "|" & arrSht1(Cnt, 4) & "|" & arrSht1(Cnt, 5) & "|" & arrSht1(Cnt, 6)
( Note the “|” is just for my convenience so that I can see easier the data, as I showed you before https://imgur.com/8tw5L61 , http://www.excelfox.com/forum/showth...tabs#post10744 )
But now we see we are looking at 5 columns ,
1 2 _ 4 5 6 corresponding to B & C _ & _ E & F & G:
5 Column extended cocatenated String comparison data.JPG : https://imgur.com/ZQ6hsIA
5 Column extended cocatenated String comparison data.JPG

***Other modifications I have done concern the test Output. These we will probably want to change later for the specific output format we require. I have already explained to you some of the ways to modify the actual results format. As I mentioned previously, at this testing stage, the exact results format is not so important

So new code at this stage is here:
http://www.excelfox.com/forum/showth...0752#post10752

Here are some test results:
http://www.excelfox.com/forum/showth...0751#post10751
( note a typo in your data for row 9 : Angle is not Angel . hence this is taken by my code as Missing data row )

_._____________________

Report back to me and we will take the project further. Important at this stage is to get the actual result info correct. ( see Note below ) The exact format can be done later***.

Alan

_.____________________________________-
P.S.
Note:
Note one extra point you may need to consider
Take the scenario of two things happening at the same time
_ that on sheet2 ( NEW ) there is new data on say, row 3
_ the data from row 3 on sheet1 ( Original) is not anywhere in sheet2 ( NEW )
In my current coding this scenario will result in the Missing data being indicated in the corresponding for row 3 in the output results. In such a scenario, the new data in row 3 on sheet2 ( NEW ) will not be indicated.
You must think about this and decide what info you want displayed on the output ( the missing or new data ) , or both. Possibly you have not thought of this scenario?

**Thainguyen** · 07-28-2018, 01:33 AM

**DocAElstein** · 07-29-2018, 01:42 PM

Hi Thai,

Originally Posted by Thainguyen

.. the way you coding your code is totally different then me

Yes, everybody writes differently so understanding somebody else code is almost always difficult.
I am happy to explain in as much detail any part of my coding. Just ask at anytime for more explanation.
The code technique, VBA arrays, which I am using might not be familiar to you. If you are not familiar with arrays, then think of arrays as extra Worksheets that only VBA can see. These “array sheets” cannot be manipulated as we can in Normal Worksheets because we cannot look at them in the same way that we can look at normal worksheets. They can only be manipulated by VBA code.
I begin by transferring all the data I need from the normal Worksheets into the initial two arrays,
arrSht1() = Ws1.Range("B1:G" & Lr1 & "").Value
arrSht2() = Ws2.Range("B1:G" & Lr1 & "").Value
I use various other arrays in the coding.
The main purpose of the code is to fill a final array, arrOut(). This final array contains the output you want. Once this is filled fully , then the entire contents of the array is pasted out in one go, in a code line looking something like this, where we adjust a Worksheet range to the size of the array, .Resize(UBound(arrOut(), 1), UBound(arrOut(), 2) , and then assign the values in the cells of the Worksheet range to the values in the array
………range …. ___ .Resize(UBound(arrOut(), 1), UBound(arrOut(), 2)).Value = arrOut()

_.________________________________________________ __

So moving on with adjusting the output to look as you wish.
To adjust this, we primarily need to change the information which the code puts in the Output array, arrout(). In other words we need to change how the code is manipulating the contents of the “hidden array sheet”, arrOut()

I suggest I give you a couple of alternatives. I will not explain them in such detail now until we have decided what output you prefer, and made any further adjustments. As I said before I will happily explain anything in a lot more detail if you ask
Alternative 1
I will use a slightly different set of test data to that which you gave in the screenshot above in Post #15. The reason for me doing this is
_1) that it will demo slightly better the two different possibilities in output data.
_2) I expect you may have intended to include in Sheet2, the data for 2 Jet Blue21 ABC 458 458. Without this data, then this will be Missing data. So without this data in Sheet2 , then I do not think your suggested output in the screenshot in post #15 is correct. I may have misunderstood. You need to clarify this…

So my start point is the data I suggest here:
http://www.excelfox.com/forum/showth...0756#post10756

In the last code , ( Sub Testies ), the following output is obtained:
http://www.excelfox.com/forum/showth...0757#post10757

So here we go..
We have currently output like this:

Test Output	Test Output	Test Output	Test Output	Test Output
2	Jet Blue23	ABC	DEF	DEF

But We want this to look more similar to screenshot output from post #15 ( http://www.excelfox.com/forum/showth...0754#post10754 )

To do this I have included a new code section, Rem3c, for a new, Third Loop @@@@@@
Briefly, this looks at the current state of each “row” in arrout().
___ If we have an entry that is not containing Missing then we add a check to see If the data is the same as in Sheet1 for the current loop row. When this is not the case this is indicated in the required _ “ xyz < > ABC “ _ type format

This code snippet is approximately the code part that does this. (I can explain in more detail anything when / if you ask):

Code:

        If InStr(1, arrOut(Cnt, 1), "MISSING:", vbBinaryCompare) <> 1 Then
        '3c(ii) Loop across columns in output array
            For Cntx = 1 To 2 ' .....we need to break up into two loops, as we have columns in Output array of 1 2 3 4 5 but in Input array for sheet 1 we have B C D E F G .. D is ignored,
                 If  arrOut(Cnt, Cntx) <> "" And arrOut(Cnt, Cntx) <> CStr(arrSht1(Cnt, Cntx)) Then ' condition for changed data
                 arrOut(Cnt, Cntx) = CStr(arrSht1(Cnt, Cntx)) & " < > " & arrOut(Cnt, Cntx)

Here is this next complete test code: http://www.excelfox.com/forum/showth...0758#post10758

Here is test output: http://www.excelfox.com/forum/showth...0759#post10759

_.________________________________________________ _

Alternative 2
I will add this possibly later, in a new post, depending on how we decide to take this further.

_.___________________________

Alan

_._______________________________

P.s. I still need an answer to this question:

Originally Posted by DocAElstein

P.S.
Note:
Note one extra point you may need to consider
Take the scenario of two things happening at the same time
_ that on sheet2 ( NEW ) there is new data on say, row 3
_ the data from row 3 on sheet1 ( Original) is not anywhere in sheet2 ( NEW )
In my current coding this scenario will result in the Missing data being indicated in the corresponding for row 3 in the output results. In such a scenario, the new data in row 3 on sheet2 ( NEW ) will not be indicated.
You must think about this and decide what info you want displayed on the output ( the missing or new data ) , or both. Possibly you have not thought of this scenario?

I may not have explained to well the question.
I will possibly try to explain again later
We can leave this question for now and answer it later.

**DocAElstein** · 07-30-2018, 12:46 PM

Alternative 2

OutputAlternative2
This is just a suggestion for an alternative output format: The main difference is that the output is given in one column. We use the single “column” concatenated check data arrays ( which we already have ) , for our output instead of using the initial capture data arrays containing all columns.

Brief code description and modifications to previous codes:
My arrOut() is now only 1 column, as I am using the concatenated string ( of all 5 column data values), in the output:
_ReDim arrOut(1 To UBound(arrSht2(), 1), 1 To 1)

I remove the “|” ( which was just for my testing convenience ) and replace that with a few spaces: “___” . This will make the displayed string look more representative of the Worksheet data
So "row" elements in my check arrays now take this type of form:
_2 _ Jet _ Blue21 _ ABC _ 458 _ 458
( previously they were this form : _2 | Jet | Blue21 | ABC | 458 | 458

The rest of the code changes basically involve changing the string data put into the arrout() to be the relevant concatenated string. So we only have one “column” of output which contains all the 5 data column values

_._____________________

Here is this alternative output code:
http://www.excelfox.com/forum/showth...0762#post10762

Here is some test results:
http://www.excelfox.com/forum/showth...0763#post10763

Alan

**DocAElstein** · 08-01-2018, 01:15 PM

Hi Thai

Originally Posted by Thai

I like this template better than the alternative #2 due to the I have like to keep all the data output in separate column. Because it looks like the layout of the Original and NEW tab layout.-

OK. No problem. We will disregard the Alternative 2
From now, we will stay with the original idea , alternative 1

_.________________________________________________ _________

Originally Posted by Thai

I noticed there is couple hiccup in the template. If you look that the result tab for JetBlue21 (at Original, B2) matching with JeBlue21 (at NEW, M10). It should not list in the output column due to it is the same data.
Also, Jet Blue23 (at NEW, M6) it should be listed as a new data in test output.
I attached your template in here too. Comapre2Worksheets July28-

This is the File that you are referring to:

Original

Test Output

NEW

Assembly #:

Assembly Name:

Assembly #:

Assembly Name:

Qty Per

Ref/Designator

Description

Customer PN

Internal PN

Manufacture PN

Qty Per

Ref/Designator

Description

Customer PN

Internal PN

Manufacture PN

1

Nu Torque

13456

456

1

Nu Torque

13456

456

1

Blu Origin

Spaceship

457

1

Blu Origin

Spaceship

457

2

Jet Blue21

ABC

458

2

Jet Blue21 < > Jet Blue23

ABC

458 < > DEF

2

Jet Blue23

ABC

DEF

3

EXCELL123

123

ABC

3

EXCELL123

123

ABC

3

Toyota

Supra

460

3

Toyota

Supra

460

2

Emirate

ABC12345

461

2

Emirate

ABC12345

461

1

Angel

ABC12346

462

MISSING: 1

MISSING: Angel

MISSING: ABC12346

MISSING: 462

2

Jet Blue21

ABC

458

Worksheet: Result
FromThai28thJuly30July.JPG : https://imgur.com/cWpDj2v
FromThai28thJuly30July.JPG

I am very confused

. I do not understand what you are saying. My code appears to give the correct results as far as I am understanding what you want.

Please do the following.
Download the uploaded attached file ( “Compare2WorkSheets July28 from Thai.xlsm”)
Add a new Sheet – call this “Results wanted”: Fill in manually exactly the results that you are wanting based on the current original and NEW data sheets.
Give me this new file. Then I will be better able to understand the results that you want.

_._____________________________

Alan