cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

program to compare 2 matrices

SOLVED
Highlighted
Granite

program to compare 2 matrices

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: program to compare 2 matrices

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.

Capture.JPG

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

Capture2.JPG

Capture3.JPG

 

 

Regards

Terry

View solution in original post

7 REPLIES 7
Highlighted

Re: program to compare 2 matrices

Hi,

 

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

 

Here is the solution given then.

 

Cheers

Terry

Highlighted

Re: program to compare 2 matrices

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.

 

Highlighted

Re: program to compare 2 matrices

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.

Highlighted

Re: program to compare 2 matrices

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

Highlighted

Re: program to compare 2 matrices

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.

Highlighted

Re: program to compare 2 matrices

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.

LucMeekes_0-1590324664161.png

 

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:

LucMeekes_1-1590325026539.png

Success!
Luc

 

 

Highlighted

Re: program to compare 2 matrices

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.

Capture.JPG

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

Capture2.JPG

Capture3.JPG

 

 

Regards

Terry

View solution in original post

Announcements