program to compare 2 matrices

[arnair81]

Hi, 

       I need to write a program to compare two matrices and eliminate non-identical rows. For example, please see attachment. In this case Matrix B can only contain numbers found in Matrix A. I need to compare each value of A with B and output values of B that matches with A (in the same order). Problem is some of the numbers in A do not appear in B and therefore, I am not able to use the match function. I would greatly appreciate any help.

Thanks.

[terryhendicott]

Hi,

This exact question (including the values) has been asked before.

Here is the solution given then.

Cheers

Terry

[Werner_E]

It looks to me that your solution is for a different problem and a different excel sheet. There is not data in the area "F3:G60" in the sheet provided by the OP.

[terryhendicott]

Hi,

It looks to me that your solution is for a different problem and a different excel sheet. There is not data in the area "F3:G60" in the sheet provided by the OP.

This post is a subset of data in a question posted earlier.  In the earlier post the data in area B2:B18 is a calibration factor.  In the earlier post data in area G3:G60 is an experimental result.  The problem is to use the right calibration factor from B3:B18 on the data in G3:G60 depending on the matching numbers in A3:A18 and F3:F60.

It is clearly a homework question where the second submitter has reduced the data set.

I enclose the first submitter's dataset.and the full answer to the homework in question.

[arnair81]

Hi Terry,

      Thanks for the reply. This is not working because, for imatch, it is telling me no matching values found. That is why I said in my initial post that the match function does not seem to be working in this case and I am not sure why. Kindly check attachment.

Regards

[Werner_E]

You could create a custom "match" function which returns whatever value (maybe NaN or 0) you want in case the built.in "match" command fails.

I am irritated by you writing "in the same order". Does this mean that 60016 would be not in the result list because it appears in A BEFORE 60029 but in B just after 60029 ?
What exact result do you expect using your test data and why do you post an excel sheet and not a simple Mathcad sheet with the two matrices already included? Would be easier to deal with.

[LucMeekes]

The attached worksheet does what you ask (per your description): It checks all values in B, compares them with values in A and outputs only those values of B that are also in A, in the order in which they appear in B.

Given your data, it is now (also) checked that ALL values in B do in fact occur in A.

Is this what you want?

For clarity, if I add 2 to all values of B, some of those sums will not be in A, the result is a smaller list:

Success!
Luc

[terryhendicott · Accepted solution]

Hi,

I take it back,  The data presented in the first set of data on 24/03/2020 is different in B.  On 24/03/2020 all values of A exist in B so my use of the match command works.

The second data set on 24/05/2020 only some values of A exist in B.  So before use of the match command each time you need to check A is in B.

You can use match safely with any number in "otherresult"

Regards

Terry