Vba button Comparison data between two file or two tabs

**Thainguyen** · 08-01-2018, 08:17 PM

**Thainguyen** · 08-01-2018, 08:17 PM

**DocAElstein** · 08-02-2018, 12:38 PM

Hi Thai
I think it is often more work to understand what is wanted than actually writing the code.
Never mind

_._______________________

Uploading files is not organised in a very clear way. Here I have done some notes for you:
http://www.excelfox.com/forum/showth...0769#post10769

_._______________________________

I will look again at the latest file you have sent me when I have time.
In the meantime a question:

Here is an extract from your last File, showing the desired results:
http://www.excelfox.com/forum/showth...0770#post10770

Here again, is just one line which is puzzling me:

Original

Test Output

NEW

1

Nu Torque

ABC

456

ABC < > 13456

1

Nu Torque

13456

456

Question is:
How do I know that this

Test Output	Test Output	Test Output	Test Output	Test Output
		ABC < > 13456

Worksheet: Result Wanted
should not be either this

Test Output	Test Output	Test Output	Test Output	Test Output
1	Nu Torque	New 13456	456	456

and/ or this:

Test Output	Test Output	Test Output	Test Output	Test Output
MISSING: 1	MISSING: Nu Torque	MISSING: ABC	MISSING: 456	MISSING: 456

**DocAElstein** · 08-02-2018, 12:38 PM

Hi Thai
I think it is often more work to understand what is wanted than actually writing the code.
Never mind

_._______________________

Uploading files is not organised in a very clear way. Here I have done some notes for you:
http://www.excelfox.com/forum/showth...0769#post10769

_._______________________________

I will look again at the latest file you have sent me when I have time.
In the meantime a question:

Here is an extract from your last File, showing the desired results:
http://www.excelfox.com/forum/showth...0770#post10770

Here again, is just one line which is puzzling me:

Original

Test Output

NEW

1

Nu Torque

ABC

456

ABC < > 13456

1

Nu Torque

13456

456

Question is:
How do I know that this

Test Output	Test Output	Test Output	Test Output	Test Output
		ABC < > 13456

Worksheet: Result Wanted
should not be either this

Test Output	Test Output	Test Output	Test Output	Test Output
1	Nu Torque	New 13456	456	456

and/ or this:

Test Output	Test Output	Test Output	Test Output	Test Output
MISSING: 1	MISSING: Nu Torque	MISSING: ABC	MISSING: 456	MISSING: 456

**DocAElstein** · 08-03-2018, 11:46 AM

Hi Thai,

Originally Posted by Thainguyen

Here is the video i found online. We could use as a good sample. The only different is they are highlighted the result.
https://www.youtube.com/watch?v=cqd0QWF9b4s

I will take a look at that video later when I have the time, .. that may answer some of my questions…
_.________________________________________________ __________________________

In the meantime, you say regarding this:

Code:

_Row\^Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q

1
Original
Original
Original
Original
Original
Original
Test Output
Test Output
Test Output
Test Output
Test Output
NEW
NEW
NEW
NEW
NEW
NEW

4
1
Nu Torque

ABC
456
456


ABC < > 13456


1
Nu Torque

13456
456
456


...so...This is the required output
 Test Output
Test Output
Test Output
Test Output
Test Output



ABC < > 13456

Here is your answer: Answer2Aug.JPG: https://imgur.com/wp8zu01

_.. so This alternative you say is OK , but not preferred:

Test Output	Test Output	Test Output	Test Output	Test Output
1	Nu Torque	New 13456	456	456

But in some previous data cases, this sort of output was the correct output.
So when and how do I know which should be the correct output? Give me please some more test data such that there is an example of both those two types of output. Show me again the results wanted. Explain to me what the criteria is for deciding when which of those two types of output is wanted
_.________________

You also say that this is incorrect

Test Output	Test Output	Test Output	Test Output	Test Output
MISSING: 1	MISSING: Nu Torque	MISSING: ABC	MISSING: 456	MISSING: 456

Please explain again what criteria will determine when this sort of output is wanted.

_.____

The problem that I have currently is that the criteria you have given me can allow for three different forms of output for the same data. It is not clear to me yet how I can decide which of those three answers should be used. Us humans can make a guess at which answer looks the most useful. But the computer program must have hard facts / criteria to use to decide when it gives which output

My initial idea would be that the program would look at how many of the 5 cells are different. If only one or two is different, then this could be taken as the original data changed and have the
” < > “
in one or two places, rather than it being indicated as
NEW: data
( Note , However if, say only one cell is different , but also the data in the NEW sheet is on a different line, then it will be impossible or extremely difficult to locate that data to make the necessary comparison: There may be other data that is similar but with only a cell or two different.

_.________________

When asking such a question as this, it is helpful if you can think very carefully about all the possible scenarios and choose very carefully the test data you give me so that all scenarios and required output is shown. ( But as always still keep the amount of test data to the minimum necessary to do this: We can discus later how to extend the code to work on much more data. It is stupid to do this until we have the code working correctly. No body will want to sort out problems using large amounts of data as this would take a ridiculous amount of time)
We are wasting a lot of time as the original test data has not been chosen to demonstrate all possible scenarios. But this is normal I guess,

.. , some testing will often reveal a scenario which was difficult to imagine initially.
Please spend some time thinking very carefully about the next test data you give me so that as many different possibilities are demonstrated in the “Results Wanted” tab. Make sure there is data that could make any of the three possibilities valid, but show data that you want to give at least one for all those three outputs, and explain carefully what criteria is used to decide which one to be used.

You need to think not as a Human, but as a boring computer.. what would it use to determine which of those three outputs is valid. We can make an intuitive guess between what is “New” and what is old data that has been changed. The computer program must make a decision based on specific criteria
_.________________________

So I wait now for some more test data from you, (including a Results Wanted worksheet) , but will also watch that video when I have time
Alan

_.________

P.S. A minor Question: Are the entries in column B in the original data unique? or could there be duplicate “Ref/Designator” ?

**DocAElstein** · 08-03-2018, 11:46 AM

Hi Thai,

Originally Posted by Thainguyen

Here is the video i found online. We could use as a good sample. The only different is they are highlighted the result.
https://www.youtube.com/watch?v=cqd0QWF9b4s

I will take a look at that video later when I have the time, .. that may answer some of my questions…
_.________________________________________________ __________________________

In the meantime, you say regarding this:

Code:

_Row\^Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q

1
Original
Original
Original
Original
Original
Original
Test Output
Test Output
Test Output
Test Output
Test Output
NEW
NEW
NEW
NEW
NEW
NEW

4
1
Nu Torque

ABC
456
456


ABC < > 13456


1
Nu Torque

13456
456
456


...so...This is the required output
 Test Output
Test Output
Test Output
Test Output
Test Output



ABC < > 13456

Here is your answer: Answer2Aug.JPG: https://imgur.com/wp8zu01

_.. so This alternative you say is OK , but not preferred:

Test Output	Test Output	Test Output	Test Output	Test Output
1	Nu Torque	New 13456	456	456

But in some previous data cases, this sort of output was the correct output.
So when and how do I know which should be the correct output? Give me please some more test data such that there is an example of both those two types of output. Show me again the results wanted. Explain to me what the criteria is for deciding when which of those two types of output is wanted
_.________________

You also say that this is incorrect

Test Output	Test Output	Test Output	Test Output	Test Output
MISSING: 1	MISSING: Nu Torque	MISSING: ABC	MISSING: 456	MISSING: 456

Please explain again what criteria will determine when this sort of output is wanted.

_.____

The problem that I have currently is that the criteria you have given me can allow for three different forms of output for the same data. It is not clear to me yet how I can decide which of those three answers should be used. Us humans can make a guess at which answer looks the most useful. But the computer program must have hard facts / criteria to use to decide when it gives which output

My initial idea would be that the program would look at how many of the 5 cells are different. If only one or two is different, then this could be taken as the original data changed and have the
” < > “
in one or two places, rather than it being indicated as
NEW: data
( Note , However if, say only one cell is different , but also the data in the NEW sheet is on a different line, then it will be impossible or extremely difficult to locate that data to make the necessary comparison: There may be other data that is similar but with only a cell or two different.

_.________________

When asking such a question as this, it is helpful if you can think very carefully about all the possible scenarios and choose very carefully the test data you give me so that all scenarios and required output is shown. ( But as always still keep the amount of test data to the minimum necessary to do this: We can discus later how to extend the code to work on much more data. It is stupid to do this until we have the code working correctly. No body will want to sort out problems using large amounts of data as this would take a ridiculous amount of time)
We are wasting a lot of time as the original test data has not been chosen to demonstrate all possible scenarios. But this is normal I guess,

.. , some testing will often reveal a scenario which was difficult to imagine initially.
Please spend some time thinking very carefully about the next test data you give me so that as many different possibilities are demonstrated in the “Results Wanted” tab. Make sure there is data that could make any of the three possibilities valid, but show data that you want to give at least one for all those three outputs, and explain carefully what criteria is used to decide which one to be used.

You need to think not as a Human, but as a boring computer.. what would it use to determine which of those three outputs is valid. We can make an intuitive guess between what is “New” and what is old data that has been changed. The computer program must make a decision based on specific criteria
_.________________________

So I wait now for some more test data from you, (including a Results Wanted worksheet) , but will also watch that video when I have time
Alan

_.________

P.S. A minor Question: Are the entries in column B in the original data unique? or could there be duplicate “Ref/Designator” ?