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.
Solved! Go to 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
Hi,
This exact question (including the values) has been asked before.
Here is the solution given then.
Cheers
Terry
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.
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.
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
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.
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
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